Stored Procedures andFunctionsRose-Hulman Institute of TechnologyCurt CliftonOutline Stored Procedures or “Sprocs” Functions Statements ReferenceDefining Stored Procedures Named Collections of Transact-SQLStatements Accept Input Parameters and Return Values Return Status Value to Indicate Success orFailure Encapsulate Repetitive TasksAdvantages of Stored Procedures Share Application Logic Shield Database Schema Details Provide Security Mechanisms Improve Performance Reduce Network TrafficEntries into sysobjects and syscomments tables Compiled plan placed inprocedure cacheCompilationOptimizationCreationCreationExecutionExecution(first time(first timeor recompile)or recompile)ParsingInitial Processing of SprocsExecution Plan RetrievedUnused plan is aged outExecution Plan Execution ContextSELECT *FROMdbo.memberWHEREmember_no = ?Connection 18082Connection 2Connection 3241003Subsequent Processing of SprocsUSE NorthwindGOCREATE PROC dbo.OverdueOrdersAS SELECT * FROM dbo.Orders WHERE RequiredDate < GETDATE() AND ShippedDate IS NullGOCreating Stored Procedures Create in Current Database Using the CREATEPROCEDURE (or CREATE PROC) Statement Can Make Recursive Calls (but stack is limited) Use sp_help to Display Information sp_help <procedure name>EXEC OverdueOrdersINSERT INTO CustomersEXEC EmployeeCustomerExecuting Stored Procedures Executing a Stored Procedure by Itself Executing a Stored Procedure Within anINSERT StatementGuidelines for Creating Sprocs dbo User Should Own All Stored Procedures E.g., dbo.OverdueOrders One Stored Procedure for Each Task! One Task for Each Stored Procedure! Create, Test, and Troubleshoot Avoid sp_ Prefix in Stored Procedure Names Used for system store proceduresUSE NorthwindGOALTER PROC dbo.OverdueOrdersASSELECT CONVERT(char(8), RequiredDate, 1) RequiredDate, CONVERT(char(8), OrderDate, 1) OrderDate, OrderID, CustomerID, EmployeeID FROM OrdersWHERE RequiredDate < GETDATE() AND ShippedDate IS NullORDER BY RequiredDateGO Altering and Dropping Sprocs Altering Stored Procedures Check dependencies:sp_depends dbo.OverdueOrders Dropping sprocs: DROP dbo.OverdueOrdersUsing Parameters in Sprocs Using Input Parameters Executing Using Input Parameters Returning Values Using Output ParametersCREATE PROCEDURE dbo.[Year to Year Sales] @BeginningDate DateTime, @EndingDate DateTime ASIF @BeginningDate IS NULL OR @EndingDate IS NULLBEGIN RAISERROR('NULL values are not allowed', 14, 1) RETURNENDSELECT O.ShippedDate, O.OrderID, OS.Subtotal, DATENAME(yy,ShippedDate) AS YearFROM ORDERS O INNER JOIN [Order Subtotals] OS ON O.OrderID = OS.OrderIDWHERE O.ShippedDate BETWEEN @BeginningDate AND @EndingDateGOUsing Input Parameters Validate All Incoming Parameter Values First Provide Default Values or Null ChecksEXEC AddCustomer 'ALFKI2', 'AlfredsFutterkiste', 'Maria Anders', 'SalesRepresentative', 'Obere Str. 57','Berlin', NULL, '12209', 'Germany','030-0074321'EXEC AddCustomer @CustomerID = 'ALFKI', @ContactName = 'Maria Anders', @CompanyName = 'Alfreds Futterkiste', @ContactTitle = 'Sales Representative', @Address = 'Obere Str. 57', @City = 'Berlin', @PostalCode = '12209', @Country = 'Germany', @Phone = '030-0074321' Executing Sprocs with Parms By name: By position:Returning Values: Output ParmsCREATE PROCEDURE dbo.MathTutor @m1 smallint, @m2 smallint, @result smallint OUTPUTAS SET @result = @m1 * @m2GODECLARE @answer smallintEXECUTE MathTutor 5,6, @answer OUTPUTSELECT 'The result is: ', @answerThe result is: 30Results of StoredResults of StoredProcedureProcedureExecuting StoredExecuting StoredProcedureProcedureCreating Stored Creating Stored ProcedureProcedureHandling Error Messages RETURN Statement Exits Query or ProcedureUnconditionally sp_addmessage Creates Custom Error Messages @@error Contains Error Number for Last ExecutedStatement RAISERROR Statement Returns user-defined or system error message Sets system flag to record errorWhat Is a User-defined Function? Scalar Functions (do not reference tables) Similar to a built-in function Multi-Statement Table-valued Functions Content like a stored procedure Referenced like a view In-Line Table-valued Functions Similar to a view with parameters Returns a table as the result of single SELECT statementUSE NorthwindCREATE FUNCTION fn_NonNull (@myinput nvarchar(30)) RETURNS nvarchar(30)BEGIN IF @myinput IS NULL SET @myinput = 'Not Applicable' RETURN @myinputENDCreating a User-defined FunctionALTER FUNCTION dbo.fn_NewRegion <New function content>DROP FUNCTION dbo.fn_NewRegionAltering and Dropping Functions Altering Functions Retains assigned permissions Causes the new function definition to replaceexisting definition Dropping FunctionsThree Examples of Functions Scalar User-defined Function Multi-Statement Table-valued Function In-Line Table-valued FunctionScalar User-defined Function RETURNS Clause Specifies Data Type Function Is Defined Within a BEGIN andEND Block Return Type Is Any Data Type Except text,ntext, image, cursor, or timestampExampleSELECT dbo.fn_DateFormat(GETDATE(), ':') Creating a function: Calling the function:USE NorthwindCREATE FUNCTION fn_DateFormat (@indate datetime, @separator char(1))RETURNS Nchar(20)ASBEGIN RETURN CONVERT(Nvarchar(20), datepart(mm,@indate)) + @separator + CONVERT(Nvarchar(20), datepart(dd, @indate)) + @separator + CONVERT(Nvarchar(20), datepart(yy, @indate))ENDMulti-Statement Table-valued Fn. BEGIN and END Enclose MultipleStatements RETURNS Clause Specifies table Data Type RETURNS Clause Names and Defines theTableUSE NorthwindGOCREATE FUNCTION fn_Employees (@length nvarchar(9))RETURNS @fn_Employees table (EmployeeID int PRIMARY KEY NOT NULL, [Employee Name] nvarchar(61) NOT NULL)ASBEGIN IF @length = 'ShortName' INSERT @fn_Employees SELECT EmployeeID, LastName FROM Employees ELSE IF @length = 'LongName' INSERT @fn_Employees SELECT EmployeeID, (FirstName + ' ' + LastName) FROM EmployeesRETURNENDSELECT * FROM dbo.fn_Employees('LongName')OrSELECT * FROM dbo.fn_Employees('ShortName')Example Creating the Function Calling it:In-Line Table-valued Function Content of the Function Is a SELECTStatement Do Not Use BEGIN and END RETURN Specifies table as the Data Type Format