1 / 30

Module 8: Implementing Stored Procedures

Module 8: Implementing Stored Procedures. Overview. Introducing Stored Procedures Creating, Modifying, Dropping, and Executing Stored Procedures Using Parameters in Stored Procedures Handling Error Messages Working with Stored Procedures. Lesson: Introducing Stored Procedures.

kirby-fletcher
Download Presentation

Module 8: Implementing Stored Procedures

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Module 8:Implementing StoredProcedures

  2. Overview • Introducing Stored Procedures • Creating, Modifying, Dropping, and ExecutingStored Procedures • Using Parameters in Stored Procedures • Handling Error Messages • Working with Stored Procedures

  3. Lesson: Introducing Stored Procedures • What Are Stored Procedures? • Advantages of Stored Procedures • Initial Processing of Stored Procedures • Subsequent Processing of Stored Procedures

  4. What Are Stored Procedures? • Named Collections of Transact-SQL Statements • Encapsulate Repetitive Tasks • Accept Input Parameters and Return OutputParameter Values • Return Status Value to Indicate Success or Failure • Five Types (System, Local, Temporary, Remote,and Extended)

  5. Advantages of Stored Procedures • Share Application Logic • Shield Database Schema Details • Provide Security Mechanisms • Improve Performance • Reduce Network Traffic • Reduce Vulnerability to SQL Injection Attacks

  6. Initial Processing of Stored Procedures Creation Entries into sysobjects and syscomments tables Parsing Execution(first time or recompile) Optimization Compiled plan placed inprocedure cache Compilation

  7. Subsequent Processing of Stored Procedures Execution Plan Retrieved Execution Plan Execution Context 8082 Connection 1 SELECT *FROM dbo.memberWHERE member_no = ? 24 Connection 2 1003 Connection 3 Unused plan is aged out

  8. Lesson: Creating, Modifying, Dropping, and Executing Stored Procedures • The CREATE PROCEDURE Statement • Guidelines for Creating Stored Procedures • The ALTER PROCEDURE Statement • The DROP PROCEDURE Statement • Stored Procedure Execution

  9. The CREATE PROCEDURE Statement • Create in Current Database Using the CREATE PROCEDURE Statement • Can Nest to 32 Levels • Use sp_help to Display Information USE Northwind GO CREATE PROC dbo.OverdueOrders AS SELECT * FROM dbo.Orders WHERE RequiredDate < GETDATE() AND ShippedDate IS Null GO

  10. Guidelines for Creating Stored Procedures dbo User Should Own All Objects ü One Stored Procedure for One Task ü Create, Test, and Troubleshoot ü Avoid sp_ Prefix in Stored Procedure Names ü Use Same Connection Settings for AllStored Procedures ü Minimize Use of Temporary Stored Procedures ü

  11. The ALTER PROCEDURE Statement • Altering Stored Procedures • Include any options in ALTER PROCEDURE • Does not affect nested stored procedures USE Northwind GO ALTER PROC dbo.OverdueOrders AS SELECT CONVERT(char(8), RequiredDate, 1) RequiredDate, CONVERT(char(8), OrderDate, 1) OrderDate, OrderID, CustomerID, EmployeeID FROM Orders WHERE RequiredDate < GETDATE() AND ShippedDate IS Null ORDER BY RequiredDate GO

  12. The DROP PROCEDURE Statement • Dropping Stored Procedures • Execute the sp_depends stored procedure to determine whether objects depend on the stored procedure • Procedure information is removed from the sysobjects and syscomments system tables • Required Permission • Procedure owner • Members of db_owner,db_ddladmin, and sysadmin roles USE Northwind GO DROP PROC dbo.OverdueOrders GO

  13. Stored Procedure Execution • Executing a Stored Procedure by Itself • Executing a Stored Procedure Within anINSERT Statement EXEC OverdueOrders INSERT INTO Customers EXEC EmployeeCustomer

  14. Lab A: Creating Stored Procedures • Exercise 1: Writing and Executing a Stored Procedure • Exercise 2: Locating StoredProcedure Information

  15. Lesson: Using Parameters in Stored Procedures • Input Parameters • Methods of Setting Parameter Values • Return Values Using OUTPUT Parameters • Return Values Using the RETURN Statement • Stored Procedure Recompile

  16. Input Parameters CREATE PROCEDURE dbo.[Year to Year Sales] @BeginDate DateTime = Null, @EndDate DateTime = Null AS IF @BeginDate IS Null SET @BeginDate = dateadd(yy,-1,GetDate()) IF @EndDate IS Null SET @EndDate = GetDate() IF Datediff(dd,@BeginDate,@EndDate) > 365 BEGIN RAISERROR('The maximum timespan allowed for this report is one year.', 14, 1) RETURN END SELECTO.ShippedDate,O.OrderID,OS.Subtotal, DATENAME(yy,ShippedDate) AS Year FROM ORDERS O INNER JOIN [Order Subtotals] OS ON O.OrderID = OS.OrderID WHERE O.ShippedDate BETWEEN @BeginDate AND @EndDate GO • Validate All Incoming ParameterValues First • Provide Appropriate Default Values and IncludeNull Checks

  17. Methods of Setting Parameter Values • Passing Values by Parameter Name • Passing Values by Position 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' EXEC AddCustomer 'ALFKI2', 'Alfreds Futterkiste', 'Maria Anders', 'Sales Representative', 'Obere Str. 57', 'Berlin', NULL, '12209', 'Germany', '030-0074321'

  18. Return Values Using OUTPUT Parameters CREATE PROCEDURE dbo.MathTutor @m1 smallint, @m2 smallint, @result int OUTPUT AS SET @result = @m1 * @m2 GO DECLARE @answer smallint EXECUTE MathTutor 5,6, @answer OUTPUT SELECT 'The result is: ', @answer The result is: 30 Creating Stored Procedure Executing Stored Procedure Results of Stored Procedure

  19. Return Values Using the RETURN Statement CREATE PROC dbo.NewEmployee( @LastName nvarchar(20), @FirstName nvarchar(10) ) AS INSERT Employees(LastName,FirstName) VALUES (@LastName, @FirstName) RETURN SCOPE_IDENTITY() Go DECLARE @NewEmployeeId int EXEC @NewEmployeeId = dbo.NewEmployee @LastName='Hankin', @FirsName='Alex' SELECT EmployeeID, LastName, FirstName FROM Employees WHERE EmployeeId = @NewEmployeeId EmployeeID LastName FirstName ----------- -------------------- ---------- 10 Hankin Alex Creating Stored Procedure Executing Stored Procedure Result

  20. Stored Procedure Recompile • Recompile When • Stored procedure returns widely varying result sets • A new index is added to an underlying table • The parameter value is atypical • Recompile by Using • CREATE PROCEDURE [WITH RECOMPILE] • EXECUTE [WITH RECOMPILE] • sp_recompile

  21. Lesson: Handling Error Messages • Error Messages • Demonstration: Handling Error Messages

  22. Error Messages • RETURN Statement Exits Query orProcedure Unconditionally • sp_addmessage Creates Custom Error Messages • @@error Contains Error Number for LastExecuted Statement • RAISERROR Statement • Returns user-defined or system error message • Sets system flag to record error

  23. Demonstration: Handling Error Messages Handling error messages

  24. Lesson: Working with Stored Procedures • Dynamic SQL in Stored Procedures • SQL Injection • Extended Stored Procedures • Performance Diagnosis Tools • Best Practices

  25. Dynamic SQL in Stored Procedures • Dynamic Search Conditions • The IN Clause • Administrative Functions SELECT @str = 'SELECT * FROM CUSTOMERS WHERE 1=1' IF LEN(@WhereCondition) > 0 SELECT @str = @str + @WhereCondition EXEC sp_executesql @str SELECT @SQL = 'SELECT ProductID, ProductName, UnitPrice FROM Products WHERE ProductID IN (' + (@ProductIDs) + ')'

  26. SQL Injection • A Technique to Inject SQL Command as an Input • Caused by Passing User Input Directly to SQL Code • How to Avoid SQL Injection • Never trust user input • Avoid dynamic SQL • Execute with least privilege • Store secrets securely • Exceptions should divulge minimal information

  27. Extended Stored Procedures • Characteristics of Extended Stored Procedures: • Programmed using open data services API • Can include C and Microsoft Visual C++ features • Can contain multiple functions • Can be called from a client or SQL server • Can be added to the master database only EXEC master..xp_cmdshell 'dir c:\'

  28. Performance Diagnosis Tools • Windows 2000 System Monitor • Object: SQL Server: Cache Manager • Object: SQL Statistics • SQL Profiler • Can monitor events • Can test each statement in a stored procedure

  29. Verify Input Parameters ü Design Each Stored Procedure to Accomplish a Single Task ü Validate Data BeforeYouBegin Transactions ü Use the Same Connection Settings for AllStored Procedures ü Use WITH ENCRYPTION to Hide Text ofStored Procedures ü Best Practices

  30. Lab B: Creating Stored Procedures Using Parameters • Exercise 1: Using the Create Stored Procedure Wizard • Exercise 2: Using Error Handling inStored Procedures • Exercise 3: Customizing Error Messages • Exercise 4: Using Return Codes • If Time Permits • Executing Extended Stored Procedures • Tracing Stored Procedures UsingSQL Profiler

More Related