330 likes | 503 Views
Module 9 Designing and Implementing Stored Procedures. Module Overview. Introduction to Stored Procedures Working with Stored Procedures Implementing Parameterized Stored Procedures Controlling Execution Context. Lesson 1: Introduction to Stored Procedures. What is a Stored Procedure?
E N D
Module 9 Designing and Implementing Stored Procedures
Module Overview • Introduction to Stored Procedures • Working with Stored Procedures • Implementing Parameterized Stored Procedures • Controlling Execution Context
Lesson 1: Introduction to Stored Procedures • What is a Stored Procedure? • Benefits of Stored Procedures • Working with System Stored Procedures • Statements not Permitted • Demonstration 1A: Working with System Stored Procedures and Extended Stored Procedures
What is a Stored Procedure? • When applications interact with SQL Server, there are two basic ways to execute T-SQL code: • Every statement can be issued directly by the application • Groups of statements can be stored on the server as stored procedures and given a name. The application then calls the procedures by name. • Stored procedures • Are similar to procedures or methods in other languages • Can have input parameters • Can have output parameters • Can return sets of rows • Are executed by the EXECUTE T-SQL statement • Can be created in managed code or T-SQL
Benefits of Stored Procedures • Can enhance the security of an application as they are a security boundary • Users can be given permission to execute a stored procedure without permission to the objects it accesses • Allow for modular programming • Create once, call many times and from many applications • Allow for delayed binding of objects • Can create a stored procedure that references a database object that doesn't exist yet. • Can avoid the need for ordering in object creation • Can improve performance • Single statement requested across the network can execute hundreds of lines of T-SQL code • Better opportunities for execution plan reuse
Working with System Stored Procedures • Large number of system stored procedures is supplied with SQL Server • Two basic types of system stored procedure: • System Stored Procedures – typically used for administrative purposes to either configure servers, databases or objects or to view information about them. • System Extended Stored Procedures – extend the functionality of SQL Server. • Key difference is how they are coded: • System Stored Procedures are T-SQL code in the master database • System Extended Stored Procedures are references to DLLs
Statements not Permitted • Not all T-SQL statements are permitted in stored procedures. • In particular, the following list are not permitted:
Demonstration 1A: Working with System Stored Procedures and Extended Stored Procedures In this demonstration you will see: • How to execute system stored procedures • How to execute system extended stored procedures
Lesson 2: Working with Stored Procedures • Creating a Stored Procedure • Executing Stored Procedures • Altering a Stored Procedure • Dropping a Stored Procedure • Stored Procedure Dependencies • Guidelines for Creating Stored Procedures • Obfuscating Stored Procedure Definitions • Demonstration 2A: Stored Procedures
Creating a Stored Procedure • CREATE PROCEDURE is used to create new stored procedures • The procedure must not already exist, otherwise ALTER must be used or the procedure dropped first • CREATE PROCEDURE must be the only statement in a batch CREATEPROCEDURESales.GetSalespersonNames AS SELECTs.BusinessEntityID,p.LastName,p.FirstName FROMSales.SalespersonAS s INNERJOINPerson.PersonAS p ONs.BusinessEntityID=p.BusinessEntityID WHEREs.TerritoryIDISNOTNULL ORDERBYs.BusinessEntityID;
Executing Stored Procedures • EXECUTE statement: • Used to execute stored procedures and other objects such as dynamic SQL statements stored in a string • Can execute system stored procedures (sp_ prefix) from within the master database without having to refer to that database. • Use two part naming when executing local stored procedures within a database. Otherwise, SQL Server searches for the procedure: • In the sys schema of the current database • In the caller's default schema in the current database • In the dbo schema in the current database EXECSales.GetSalespersonNames;
Altering a Stored Procedure • ALTER PROCEDURE • Used to replace a stored procedure • Retains the existing permissions on the procedure ALTERPROCEDURESales.GetSalespersonNames AS SELECTs.BusinessEntityID,p.LastName,p.FirstName FROMSales.SalespersonAS s INNERJOINPerson.PersonAS p ONs.BusinessEntityID=p.BusinessEntityID WHEREs.TerritoryIDISNOTNULL ANDs.SalesQuotaISNOTNULL ORDERBYs.BusinessEntityID;
Dropping a Stored Procedure • DROP PROCEDURE removes one or more stored procedures from the current database • Find the list of existing procedures in the current database by querying the sys.procedures system view • Use sp_dropextendedproc to drop Extended Stored Procedures SELECTSCHEMA_NAME(schema_id)ASSchemaName, name ASProcedureName FROMsys.procedures; GO DROPPROCEDURESales.GetSalespersonNames;
Stored Procedure Dependencies • New system views replace the use of sp_depends • sys.sql_expression_dependencies • Contains one row per by-name dependency on a user-defined entities in the current database • sys.dm_sql_referenced_entities • Contains one row for each entity referenced by another entity • sys.dm_sql_referencing_entities • Contains one row for each entity referencing another entity
Guidelines for Creating Stored Procedures Qualify names inside of stored procedures ü Keep consistent SET options ü Apply consistent naming conventions (and no sp_ prefix) ü Use @@nestlevel to see current nesting level (32 max) ü Keep one procedure per task ü
Obfuscating Stored Procedure Definitions • WITH ENCRYPTION clause • Encrypts stored procedure definition stored in SQL Server • Protects stored procedure creation logic to a limited extent • Is generally not recommended CREATEPROCEDUREHumanResources.EmployeeList WITHENCRYPTION AS SELECTEmployeeID,LastName,FirstName FROMHumanResources.Employee; Use WITH ENCRYPTION on ALTER PROC to retain encryption
Demonstration 2A: Stored Procedures • In this demonstration, you will see: • How to create a stored procedure • How to execute a stored procedure • How to create a stored procedure that returns multiple rowsets • How to alter a stored procedure • How to view the list of stored procedures
Lesson 3: Implementing Parameterized Stored Procedures • Working with Parameterized Stored Procedures • Using Input Parameters • Using Output Parameters • Parameter Sniffing and Performance • Demonstration 3A: Stored Procedure Parameters
Working with Parameterized Stored Procedures Parameterized stored procedures contain 3 major components: Input parameters Output parameters Return values
Using Input Parameters • Parameters • Have @ prefix, data type, can have a default value • Can be passed in order or can be passed by name (but no combination of these is permitted in one statement) • Validate input parameters early in stored procedure code CREATEPROCEDURESales.OrdersByDueDateAndStatus @DueDate datetime, @Status tinyint= 5 AS SELECTsoh.SalesOrderID,soh.OrderDate,soh.CustomerID FROMSales.SalesOrderHeaderAS soh WHEREsoh.DueDate= @DueDate AND soh.[Status] = @Status ORDERBYsoh.SalesOrderID; GO EXECSales.OrdersByDueDateAndStatus'20050713',5; EXECSales.OrdersByDueDateAndStatus'20050713'; EXECSales.OrdersByDueDateAndStatus@DueDate='20050713', @Status = 5;
Using Output Parameters • OUTPUT must be specified: • When declaring the parameter • When executing the stored procedure CREATEPROCSales.GetOrderCountByDueDate @DueDatedatetime, @OrderCountintOUTPUT AS SELECT @OrderCount=COUNT(1) FROMSales.SalesOrderHeaderAS soh WHEREsoh.DueDate= @DueDate; GO DECLARE @DueDatedatetime='20050713'; DECLARE @OrderCountint; EXECSales.GetOrderCountByDueDate@DueDate, @OrderCountOUTPUT; SELECT @OrderCount;
Parameter Sniffing and Performance • Query plan generated for a stored procedure is mostly reused the next time the stored procedure is executed • In general, this is very desirable behavior • Some stored procedures need to have very different query plans for different sets of parameters before they will perform optimally • Problem is commonly called a "parameter sniffing" problem • Options for resolving • CREATE PROC xyz WITH RECOMPILE • sp_recompile 'xyz' • EXEC WITH RECOMPILE • OPTION (OPTIMIZE FOR)
Demonstration 3A: Stored Procedure Parameters In this demonstration you will see: • How to create a stored procedure with parameters • How to alter a stored procedure with parameters to correct a common stored procedure bug
Lesson 4: Controlling Execution Context • Controlling Execution Context • The EXECUTE AS Clause • Viewing Execution Context • Demonstration 4A: Viewing Execution Context
Controlling Execution Context Ted (No permissions) Pat (SELECT permission) Sales.SalesOrderHeader (Owner: John) GetOrderCountByDueDate Ted (EXECUTE permission) Procedure (Owner: Pat) Pat CREATEPROCSales.GetOrderCountByDueDate @DueDatedatetime, @OrderCountintOUTPUT WITHEXECUTEAS'Pat' AS SELECT @OrderCount=COUNT(1) FROMSales.SalesOrderHeaderAS soh WHEREsoh.DueDate= @DueDate; CREATEPROCSales.GetOrderCountByDueDate @DueDatedatetime, @OrderCountintOUTPUT AS SELECT @OrderCount=COUNT(1) FROMSales.SalesOrderHeaderAS soh WHEREsoh.DueDate= @DueDate;
The EXECUTE AS Clause Enables Impersonation ü Provides access to modules via impersonation ü Can be used to impersonate server-level principals or logins via the EXECUTE AS LOGIN statement ü Can be used to impersonate database level principals or users via the EXECUTE AS USER statement ü CREATEPROCEDURESales.GetOrders WITHEXECUTEAS{CALLER|SELF|OWNER|‘user_name’} AS …
Viewing Execution Context • Details of the current security context can be viewed programmatically • sys.login_token shows the login-related details • sys.user_token shows the user-related details
Demonstration 4A: Viewing Execution Context In this demonstration you will see: • How to view details of execution context • How to change execution context for a session • How to use the WITH EXECUTE AS clause in a stored procedure
Lab 9: Designing and Implementing Stored Procedures • Exercise 1: Create stored procedures • Exercise 2: Create a parameterized stored procedure • Challenge Exercise 3: Alter the execution context of stored procedures (Only if time permits) Logon information Estimated time: 45minutes
Lab Scenario You need to create a set of stored procedures to support a new reporting application. The procedures will be created within a new Reports schema.
Lab Review • When is the OUTPUT keyword needed for output parameters in working with stored procedures? • What does the sys.login_token view show?
Module Review and Takeaways • Review Questions • Best Practices