1 / 31

Understanding Stored Procedures in SQL Server

Learn about different types of stored procedures like System SP, Extended SP, and how to create, execute, modify, and delete stored procedures in SQL Server. Manage errors using sp_addmessage, RAISERROR, and @@ERROR functions.

rosina
Download Presentation

Understanding Stored Procedures in SQL Server

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 9 (tt) STORED PROCEDURES AND FUNCTION (9.6.1)

  2. Introduction • Stored procedure (SP): is a segment of code which contains declarative or procedural SQL statements. • A stored procedure is resided in the catalog of the database server so we can call it from a trigger, another stored procedure or even from client applications. • Stored procedures are essentially functions that you can create in the database and reuse. They can take input parameters and then return a result

  3. Stored procedures type • System SP (sp): is stored in the Master database, but can be executed in any database without using its full name. • sp_helptext: Prints the text of a rule, a default, or an unencrypted stored procedure, user-defined function, trigger, computed column, or view. Example : master.dbo.sp_helptext • sp_help: Reports information about a database object • sp_depends: Displays information about database object dependencies the view(s), trigger(s), and procedure(s)—in the database that depend on a specified table or view,

  4. Stored procedures type • Extended SP (xp): is created from other languages (C++,...) and used as a procedure of SQL Server User_defined : • Local sp: is an object in the database to execute the tasks. It can be created in master db. • Temporary sp: local (with the name begun by #) and global (with the name begun by ##).

  5. Create stored procedures • Syntax: CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type} [ VARYING ] [ = default ] [ OUTPUT ] ] [,...n ] [ WITH  { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement[ ...n ]

  6. Create stored procedures • Example: CREATE PROCEDURE OrderSummary AS SELECT Ord.EmployeeID, SummSales=SUM(OrDet.UnitPrice*OrDet.Quantity) FROM Orders AS Ord JOIN [Order Details] AS OrDet ON (Ord.OrderID = OrDet.OrderID) GROUP BY Ord.EmployeeID

  7. Execute stored Procedures • Execute: Executes user-defined function, a system procedure, a user-defined stored procedure, or an extended stored procedure. • Syntax: [ [ EXEC [ UTE ] ] {[ @return_status= ] { procedure_name[ ;number ] | @procedure_name_var    }     [ [ @parameter= ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ] [,...n ] [ WITH RECOMPILE ]

  8. Execute stored Procedures • Or • Example: EXECUTE dbo.overdueOrders EXECUTEProductName[ ; number ] [<parameter>[, …n][ OUTPUT ]]

  9. Modify stored Procedures • Syntax: ALTER PROCEDURE procedure_name [WITH option] AS sql_statement [...n]

  10. Modify stored Procedures • Example: ALTER PROC dbo.overdueOrders AS SELECTCONVERT(CHAR(8), RequiredDate,1) RequiredDate, CONVERT(CHAR(8), orderDate,1) orderDate, orderId, Customerid, EmployeeID FROMdbo.orders WHERERequiredDate<GETDATE()and shippeddate is null ORDER BY RequiredDate

  11. Delete stored Procedures • Syntax: • DROP PROC owner.stored_procedure_name

  12. Using parameter in stored Procedures • Input parameter: CREATE PROCEDURE procedure_name [@parameter_namedata_type] [=default_value] [WITH option] AS sql_statement[...n]

  13. Using parameter in stored Procedures • Example 1: CREATE PROC dbo.MovieByRating @rating varchar(5) = NULL AS SELECT rating , title FROM movie WHERE rating = @rating ORDER BY title

  14. Using parameter in stored Procedures • Example2 : CREATE PROC sp_name @parameter data_type =value AS IF @parameter IS NULL BEGIN PRINT ‘Message Line 1’ PRINT ‘Message Line 2’ RETURN END SELECT statement GO

  15. Using parameter in stored Procedures • Output parameter: CREATE PROCEDURE procedure_name [@parameter_namedata_type] [=default_value] OUTPUT [WITH option] AS sql_statement [...n]

  16. Using parameter in stored Procedures • Example: CREATE PROC count_row @movie_countint OUTPUT AS SELECT @movie_count = COUNT(*) FROM Movie GO • Sp execution with output parameter: a variable must be declared to stored the return value of the output parameter DECLARE @numint EXEC count_row @num OUTPUT SELECT @num

  17. Errors management • sp_addmessage: Stores a new user-defined error message in an instance of the SQL Server Database Engine. Messages can be viewed by using the sys.messages. • Syntax: sp_addmessage [ @msgnum= ] msg_id , [ @severity= ] severity , [ @msgtext= ] 'msg' [ , [ @lang= ] 'language' ] [ , [ @with_log= ] { 'TRUE' | 'FALSE' } ] [ , [ @replace= ] 'replace' ]

  18. Errors management Example: Create an error message by sp_addmessage EXEC sp_addmessage @msgnum = 50001, @severity = 10, @msgtext=‘Cannot delete customer. Customer has orders .’, @withlog = ‘true’ GO

  19. Errors management VD: Display an error message when delete a customer CREATE PROC DeleteCust @cust_numnvarchar(5) = null AS IF EXISTS (SELECT customerID FROM Orders WHERE customerID like @cust_num) BEGIN RAISERROR (50001, 10, 1) RETURN END DELETE FROM Customers WHERE customerID like @cust_num GO

  20. Errors management • @@ERROR: Returns an error number if the previous statement encountered an error. Example: USE AdventureWorks2008R2; GO UPDATE HumanResources.EmployeePayHistory SET PayFrequency = 4 WHERE BusinessEntityID = 1; IF @@ERROR = 547 PRINT N'A check constraint violation occurred.'; GO

  21. Errors management • @@ERROR and @@ROWCOUNT: Use to validate the operation of an UPDATE statement. • The value of @@ERROR is checked for any indication of an error. • The value of @@ROWCOUNT is used to ensure that the update was successfully applied to a row in the table.

  22. FUNCTION • System function: • aggregate funtion: avg(), count(), count(*), sum(), max(), min(),... • Other function: getdate(), month(), upper(), user_name(),@@rowcount,... • User-defined function: • Allow you to define your own T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.

  23. FUNCTION • There are 3 type User-defined function: • Scalar: return a single value, based on the input value. • Multi-statement Table-valued: return a set of row • Inline Table-valued: return a set of row

  24. FUNCTION • Scalar function: CREATE  FUNCTION [ owner_name. ] function_name( [ { @parameter_name [AS] scalar_parameter_data_type[= default ] } [ ,...n ] ] ) RETURNSscalar_return_data_type. [WITH < function_option> [ [,] ...n] ] [AS ] BEGIN function_body    RETURN scalar_expressionEND

  25. FUNCTION • Example: CREATE FUNCTION dbo.OrderNum(@monthOrdtinyint ) RETURNS tinyint AS BEGIN DECLARE @Ordnumtinyint SELECT @Ordnum = count(orderid) FROM Orders WHERE month(orderdate)= @monthOrd RETURN @Ordnum END GO

  26. FUNCTION • Execute: • SELECT dbo.OrderNum(7) • Function can be used in the “Where” clause Select orderid from orders where dbo.OrderNum(7) > 50 and month(orderdate)=7

  27. FUNCTION • Table-valued Functions CREATE FUNCTION [ owner_name. ] function_name([{ @parameter_name [AS] scalar_parameter_data_type[= default ] } [,...n ] ]) RETURNSTABLE [WITH < function_option > [ [,] ...n ] ] [AS ] RETURN [(] select-stmt[)]

  28. FUNCTION • Example: CREATE FUNCTION SalesByCategory(@CategoryidInt) RETURNS TABLE AS RETURN (SELECT c.CategoryName, P. ProductName, SUM(Quantity) AS TotalQty FROM Categories c INNER JOIN Products p ON c.CategoryID= p. CategoryID INNER JOIN [Order Details] od ON p.ProductID = od.ProductID WHERE c.CategoryID= @Categoryid GROUP BY c. CategoryName,p.ProductName)

  29. FUNCTION • Multistatement Table-valuesd • CREATE FUNCTION [owner_name.]function_name • ([{@parameter_name [AS] data_type [=default]} [ ,…n ]]) • RETURNS@return_variable • TABLE ({column_definition | table_constraint} [ ,…n ]) • [WITH { ENCRYPTION | SCHEMABINDING } [ [,] ...n] ] • [AS] • BEGIN • function_body • RETURN • END

  30. FUNCTION • Example: CREATE FUNCTION Contacts(@suppliers bit=0) RETURNS @Contacts TABLE (ContactNamenvarchar(30), Phone nvarchar(24), ContactTypenvarchar(15)) AS BEGIN INSERT @Contacts SELECT ContactName, Phone, 'Customer' FROM Customers INSERT @Contacts SELECT FirstName + ' ' + LastName, HomePhone, 'Employee' FROM Employees IF @Suppliers=1 INSERT @Contacts SELECT ContactName, Phone, 'Supplier‘ FROM Suppliers RETURN END

  31. FUNCTION • Execute: SELECT * FROM CONTACTS(1) ORDER BY ContactName

More Related