New version page

Rose-Hulman CSSE 333 - Stored Procedures and Functions

Upgrade to remove ads
Upgrade to remove ads
Unformatted text preview:

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

View Full Document
Download Stored Procedures and Functions
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...

Join to view Stored Procedures and Functions and access 3M+ class-specific study document.

We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Stored Procedures and Functions 2 2 and access 3M+ class-specific study document.


By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?