Chapter 9 (tt) Stored procedures and function (9.6.1)

Chapter 9 (tt) STORED PROCEDURES AND FUNCTION (9.6.1) 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 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, 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 ##). 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 ] 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 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 ] Execute stored Procedures Or Example: EXECUTE dbo.overdueOrders EXECUTE ProductName [ ; number ] [[, n][ OUTPUT ]] Modify stored Procedures Syntax: ALTER PROCEDURE procedure_name [WITH option] AS sql_statement [...n] Modify stored Procedures Example: ALTER PROC dbo.overdueOrders AS SELECT CONVERT(CHAR(8), RequiredDate,1) RequiredDate, CONVERT(CHAR(8), orderDate,1) orderDate, orderId, Customerid, EmployeeID FROM dbo.orders WHERE RequiredDate<GETDATE()and shippeddate is null ORDER BY RequiredDate Delete stored Procedures Syntax: DROP PROC owner.stored_procedure_name Using parameter in stored Procedures Input parameter: CREATE PROCEDURE procedure_name [@parameter_name data_type] [=default_value] [WITH option] AS sql_statement [...n] 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 Using parameter in stored Procedures  Example 2 : 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 Using parameter in stored Procedures Output parameter: CREATE PROCEDURE procedure_name [@parameter_name data_type] [=default_value] OUTPUT [WITH option] AS sql_statement [...n] Using parameter in stored Procedures  Example: CREATE PROC count_row @movie_count int 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 @num int EXEC count_row @num OUTPUT SELECT @num 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' ] 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 Errors management VD: Display an error message when delete a customer CREATE PROC DeleteCust @cust_num nvarchar(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 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 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. 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. 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 FUNCTION Scalar function: CREATE FUNCTION [ owner_name. ] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [= default ] } [ ,...n ] ] ) RETURNS scalar_return_data_type. [WITH [ [,] ...n] ] [AS ] BEGIN function_body RETURN scalar_expression END FUNCTION Example: CREATE FUNCTION dbo.OrderNum (@monthOrd tinyint ) RETURNS tinyint AS BEGIN DECLARE @Ordnum tinyint SELECT @Ordnum = count(orderid) FROM Orders WHERE month(orderdate)= @monthOrd RETURN @Ordnum END GO 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 FUNCTION Table-valued Functions CREATE FUNCTION [ owner_name. ] function_name ([{ @parameter_name [AS] scalar_parameter_data_type [= default ] } [,...n ] ]) RETURNS TABLE [WITH [ [,] ...n ] ] [AS ] RETURN [(] select-stmt [)] FUNCTION Example: CREATE FUNCTION SalesByCategory(@Categoryid Int) 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) 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 FUNCTION  Example: CREATE FUNCTION Contacts(@suppliers bit=0) RETURNS @Contacts TABLE (ContactName nvarchar(30), Phone nvarchar(24), ContactType nvarchar(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 FUNCTION Execute: SELECT * FROM CONTACTS(1) ORDER BY ContactName