Module 8 Implementing Stored Procedures Overview Implementing Stored

Module 8: Implementing Stored Procedures

Overview Implementing Stored Procedures Creating Parameterized Stored Procedures Working With Execution Plans Handling Errors

Lesson 1: Implementing Stored Procedures What Is a Stored Procedure? Syntax for Creating Stored Procedures Guidelines for Creating Stored Procedures Syntax for Altering and Dropping Stored Procedures

What Is a Stored Procedure? A named collection of Transact-SQL statements or Microsoft. NET Framework code Accepts input parameters and returns output parameter values Returns status value to indicate success or failure

Syntax for Creating Stored Procedures Create in current database by using the CREATE PROCEDURE statement CREATE PROCEDURE Production. Long. Lead. Products AS SELECT Name, Product. Number FROM Production. Product WHERE Days. To. Manufacture >= 1 GO Use EXECUTE to run stored procedure EXECUTE Production. Long. Lead. Products

Guidelines for Creating Stored Procedures ü Qualify object names inside procedure ü Create one stored procedure for one task ü Create, test, and troubleshoot ü Avoid sp_ prefix in stored procedure names Use consistent connection settings for all ü stored procedures Minimize use of temporary stored ü procedures

Syntax for Altering and Dropping Stored Procedures ALTER PROCEDURE ALTER PROC Production. Long. Lead. Products AS SELECT Name, Product. Number, Days. To. Manufacture FROM Production. Product WHERE Days. To. Manufacture >= 1 ORDER BY Days. To. Manufacture DESC, Name GO DROP PROCEDURE DROP PROC Production. Long. Lead. Products

Lesson 2: Creating Parameterized Stored Procedures Input Parameters Output Parameters and Return Values Practice: Creating a Parameterized Stored Procedure

Input Parameters Provide appropriate default values Validate incoming parameter values, including null checks ALTER PROC Production. Long. Lead. Products AS @Minimum. Length int = 1 -- default value IF (@Minimum. Length < 0) -- validate BEGIN RAISERROR('Invalid lead time. ', 14, 1) RETURN END SELECT Name, Product. Number, Days. To. Manufacture FROM Production. Product WHERE Days. To. Manufacture >= @Minimum. Length ORDER BY Days. To. Manufacture DESC, Name EXEC Production. Long. Lead. Products @Minimum. Length=4

Output Parameters and Return Values CREATE PROC Human. Resources. Add. Department @Name nvarchar(50), @Group. Name nvarchar(50), @Dept. ID smallint OUTPUT AS IF ((@Name = '') OR (@Group. Name = '')) RETURN -1 INSERT INTO Human. Resources. Department (Name, Group. Name) VALUES (@Name, @Group. Name) SET @Dept. ID = SCOPE_IDENTITY() RETURN 0 DECLARE @dept int, int @result int EXEC @result = Add. Department '', @dept OUTPUT Add. Department 'Refunds', '', @dept OUTPUT IF (@result = 0) SELECT @dept ELSE SELECT 'Error during insert'

Practice: Creating a Parameterized Stored Procedure In this practice, you will: Create a simple stored procedure Create a stored procedure that accepts an input parameter Create a stored procedure that accepts an output parameter and returns values Drop a stored procedure

Lesson 3: Working With Execution Plans What Is an Execution Plan? Viewing an Execution Plan Caching Query Compilation Forcing Stored Procedure Recompilation

What Is an Execution Plan? Shows the actual or estimated execution of a query Performance and Optimization Table or index scans Bookmark lookups Filter Sort

Viewing an Execution Plan SQL Server Management Studio 1 Open or type a Transact-SQL script 2 Click Display Estimated Execution Plan or Include Actual Execution Plan button 3 Query is parsed or executed 4 Click the Execution plan tab Transact-SQL SET SHOWPLAN_XML ON SET STATISTICS XML ON SET SHOWPLAN_TEXT ON SET STATISTICS PROFILE ON

Execution Plan Caching Execution context connection 1 Parameter value: 12 Execution context connection 2 Parameter value: 24 Query plan SELECT * FROM My. Table WHERE Pri. Key = ? Execution context connection 3 Parameter value: 36 Procedure cache Age Cost

Query Compilation Parsing Normalization Compilation Optimization

Forcing Stored Procedure Recompilation Stored procedure recompilation 1 sp_recompile 2 WITH RECOMPILE at creation 3 WITH RECOMPILE on execution USE Adventure. Works; GO EXEC sp_recompile N'Sales. Customer'; GO

Lesson 4: Handling Errors Syntax for Structured Exception Handling Guidelines for Handling Errors Practice: Handling Errors

Syntax for Structured Exception Handling TRY…CATCH blocks provide the structure TRY block contains protected transactions CATCH block handles errors CREATE PROCEDURE dbo. Add. Data @a int, @b int AS BEGIN TRY INSERT INTO Table. With. Key VALUES (@a, @b) END TRY BEGIN CATCH SELECT ERROR_NUMBER() Error. Number, ERROR_MESSAGE() [Message] END CATCH

Guidelines for Handling Errors ü Create CATCH block immediately after TRY ü BEGINRoll TRY back failed transactions in CATCH -- INSERT INTO. . . END TRY Consider using XACT_ABORT SELECT * FROM Table. With. Key -- NOT ALLOWED ON and BEGIN TRY BEGIN CATCH XACT_STATE BEGIN TRANERROR_NUMBER() -SELECT INSERT. . . END SET CATCH XACT_ABORT ON INSERT. . . Capture ERROR_xxx information if required BEGIN TRY COMMIT TRAN BEGIN TRAN END TRY. . . BEGIN CATCH COMMIT BEGIN TRY TRAN ROLLBACK TRAN END. . . TRY SELECT MESSAGE_NUMBER. . . BEGIN CATCH END TRY CATCH IF (XACT_STATE()) = -1 -- uncommitable BEGIN CATCH ROLLBACK INSERT INTOTRAN Error. Log ELSE IF (XACT_STATE()) = 1 ERROR_MESSAGE(), -- commitable VALUES (ERROR_NUMBER(), GETDATE()) COMMIT TRAN END CATCH ü ü

Practice: Handling Errors In this practice, you will add error handling to a stored procedure

Lab: Implementing Stored Procedures Exercise 1: Creating Stored Procedures Exercise 2: Working With Execution Plans