1 / 22

Stored Procedures in SQL Server

Stored Procedures in SQL Server. Architecture Overview Designing and Creating SP SP Variables and Return Values Odds ‘n’ Ends. Architecture Elements. A full-fledged systems production and operation environment will consist of a multitude of elements Databases Tables, relationships, indices

kimama
Download Presentation

Stored Procedures in SQL Server

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. Stored Procedures in SQL Server • Architecture Overview • Designing and Creating SP • SP Variables and Return Values • Odds ‘n’ Ends

  2. Architecture Elements • A full-fledged systems production and operation environment will consist of a multitude of elements • Databases • Tables, relationships, indices • Stored procedures • Database connectivity • System connectivity (DSN, ADO.Net) • Application connectivity • Applications

  3. Architecture Elements (cont.) • Next few weeks will be dealing with these elements • It will be important that you understand individual topics in the context of this framework

  4. Stored Procedures • Stored Procedures (SP) are procedural instructions stored within the database • SP can be called accessed by other SP or by external applications • Simplest SP execute a single SQL statement • SP can be incredibly complex • SP can accept variable values • SP can return results • Individual discrete values • Entire recordsets (query results)

  5. Script to Create a Simple Stored Procedure if exists (select name from sysobjects where name = 'up_Organization_Update' and type = 'P') drop procedure up_Organization_Update GO CREATE PROCEDURE up_Organization_Update @OrgID bigint, @OrgName varchar(50), @AcctName varchar(50), @WireAcct varchar(25) AS UPDATE Organization SET OrgName = @OrgName, AcctName = @AcctName, WireAcct = @WireAcct WHERE OrgID = @OrgID GO More on this SP later

  6. SP Advantages • Centralized • Any application can access the SP because it is stored with the database • Maintenance takes place in one location • Fast!! • DB compiles SP and develops an ‘execution plan’ the first time the SP is run • Subsequent runs are as fast as they can be • Secure • SP logic is hidden from anyone who does not have permissions to view the object Really Important

  7. Designing SP • Most tables will need INSERT INTO and UPDATE SP • Identify other SP needed for your application’s business logic • DELETE • Specialized SELECT queries • Retrieve an individual record by some criteria • Retrieve a collection of records by a criteria • Retrieve all records in a table • Perform multi-table join queries • Perform aggregate queries

  8. Designing SP (cont.) • Naming Stored Procedures • All begin with “up” for User Procedure • Rest of name should give purpose of query • Single table procedures should be “up_tablename_purpose” • Examples • up_customers_insert • up_customers_selectbyCustID • up_monthlysalesdetail_bymonth SP appear alphabeticallyin Enterprise Manager

  9. Creating SP • SP are created in Enterprise Manager • Executing the SP creation command in the Query Analyzer creates the SP as an object in the DB • SP can be modified in the Enterprise Manager or modified in the Query Analyzer and recreated • Be sure to save your SP files from Query Analyzer so they can be modified and rerun if necessary Demonstration

  10. Creating SP (cont.) • CREATE PROCEDURE procedure_name[parameter list]AS • Creates procedure • Parameter names must start with ‘@’ • Parameters are typed with SQL Server data types • Parameters should match field types CREATE PROCEDURE up_Organization_Update @OrgID bigint, @OrgName varchar(50), @AcctName varchar(50), @WireAcct varchar(25) AS

  11. Creating SP (cont.) • Body of procedureexecutes logic • Parameters are usedlike variables in theSQL statements • Note that there are no delimiters (single quotes for text or #-signs for dates) around these values UPDATE Organization SET OrgName = @OrgName, AcctName = @AcctName, WireAcct = @WireAcct WHERE OrgID = @OrgID GO

  12. SP Variables and Return Values • When SP create a recordset with an SQL SELECT statement that recordset is available to the calling procedure or application (more later) • SP may return a value with the RETURN(@varname) syntax • If @varname is not an input parameter it must be created with the DECLARE statementDECLARE @varname datatype • Use SET to assign a value to a variable • @@ERROR and @@IDENTITY are common intrinsic values that are returned

  13. SP Variables & Return Values (cont.) CREATE PROCEDURE up_Organization_Insert @OrgName varchar(50), @AcctName varchar(50), @WireAcct varchar(25) AS DECLARE @OrgID bigint --Perform the insert INSERT INTO Organization ( OrgName, AcctName, WireAcct) VALUES (@OrgName, @AcctName, @WireAcct) --Load the PK into the return parameter SET @OrgID = @@Identity RETURN (@OrgID) GO Parameters Declare internal variable @@Identity gives identityattribute value of most recently added record Returning the variable value

  14. SP Variables & Return Values (cont.) • Notes: • RETURN can only return a variable • RETURN(@@Identity) won’t work • Pay careful attention to data types • When a parameter variable or internal variable interacts directly with a table field the field and the variable must be of the same data type • Ensure that varchar variables and fields are the same length • We will see how to read returned recordset values next time

  15. Testing SP CREATE PROCEDURE up_Organization_Insert @OrgName varchar(50), @AcctName varchar(50), @WireAcct varchar(25) AS DECLARE @OrgID bigint --Perform the insert INSERT INTO Organization ( OrgName, AcctName, WireAcct) VALUES (@OrgName, @AcctName, @WireAcct) --Load the PK into the return parameter SET @OrgID = @@Identity RETURN (@OrgID) GO • Test SP in Enterprise Mgr.with the Exec statement • Exec SP_Name [parameterlist] • [Parameter List] is a listof values, one for eachparameter in the SP, in the specified order. Must have delimiters if applicable • Enter in E.M.: • EXEC up_Organization_Insert ‘Test Org’, ‘Test Acct’, ‘12345’ • SELECT * FROM Organization

  16. Testing SP (cont.) • SP may also be tested with named parameters from an Enterprise Manager query window EXEC [dbo].[up_Order_Details_Add_Test] @OrderID = 11096, @ProductID = 2, @UnitPrice = 14, @Quantity = 1, @Discount = 0

  17. CREATE PROCEDURE [up_Order_Details_Add_Test] @OrderID int, @ProductID int, @UnitPrice money, @Quantity smallint = 1, @Discount real = 0 AS --Decrement QOH in Products table UPDATE Products SET UnitsInStock = UnitsInStock - @Quantity WHERE ProductID = @ProductID --Insert into [Order Details] INSERT INTO [Order Details] VALUES( @OrderID, @ProductID, @UnitPrice, @Quantity, @Discount) Execution need not provide values for all parameters (but may) EXEC [dbo].[up_Order_Details_Add_Test] @OrderID = 11096, @ProductID = 4, @UnitPrice = 14 Default Parameter Values

  18. Use Output param to return values ALTER PROCEDURE [up_Order_Details_Add_Test] @OrderID int, @ProductID int, @UnitPrice money, @Quantity smallint = 1, @Discount real = 0, @QuantityOnHand smallint Output AS --Decrement quantity on hand in Products table UPDATE Products SET UnitsInStock = UnitsInStock - @Quantity WHERE ProductID = @ProductID -- Return QOH After Update SET @QuantityOnHand = ( SELECT UnitsInStock FROM Products WHERE ProductID = @ProductID) Illustrating calling the procedure to the left from EM DECLARE @Qty smallint EXEC [dbo].[up_Order_Details_Add_Test] @OrderID = 11096, @ProductID = 39, @UnitPrice = 14, @QuantityOnHand = @qty OUTPUT SELECT @qty May also declare parameters in .VB code to be of ParameterDirection.Output Output Parameters

  19. ALTER PROCEDURE [up_Order_Details_Add_Test] @OrderID int, @ProductID int, @UnitPrice money, @Quantity smallint = 1, @Discount real = 0, @QuantityOnHand smallint Output AS Declare @NewRec bit --Decrement quantity on hand in Products table UPDATE Products SET UnitsInStock = UnitsInStock - @Quantity WHERE ProductID = @ProductID -- Return QOH After Update SET @QuantityOnHand = ( SELECT UnitsInStock FROM Products WHERE ProductID = @ProductID) set @NewRec = (SELECT Count(*) FROM [Order Details] WHERE OrderID = @OrderID AND ProductID = @ProductID) IF @NewRec = 0 BEGIN --Perform the insert into [Order Details] table INSERT INTO [Order Details] VALUES( @OrderID, @ProductID, @UnitPrice, @Quantity, @Discount) END ELSE BEGIN -- Update existing record UPDATE [Order Details] SET Quantity = Quantity + @Quantity WHERE OrderID = @OrderID AND ProductID = @ProductID END Flow Control with If [Else]

  20. CREATE FUNCTION [dbo].[udf_Orders_LastCustOrderDate] ( @CustomerID nchar(5) ) RETURNS datetime AS BEGIN -- Declare the return datetime variable DECLARE @LastOrderDate datetime -- Select the last date into the return variable SET @LastOrderDate = ( SELECT Max(OrderDate) FROM Orders WHERE CustomerID = @CustomerID) -- Return the result of the function RETURN @LastOrderDate END Using the Function in a SP Must use the dbo.function_name format May have multiple parameters just like a VB function SELECT CustomerID, CompanyName, dbo.udf_Orders_LastCustOrderDate(CustomerID) AS [Last Order] FROM Customers WHERE dbo.udf_Orders_LastCustOrderDate(CustomerID) < '1/1/2007' User Defined Functions

  21. More on SP • SP can actually be incredibly rich procedural code using T-SQL (transact SQL) • Conditional execution • Looping execution • Branching execution • Calling other SP (reusable logic modules) • Oracle and other DB have similar capabilities • Most common SP execute discrete DB activities based around SELECT, INSERT INTO, UPDATE, and DELETE statements

  22. More on SP (cont.) • It is absolutely imperative that you practice these steps • We are now moving into programming technologies • You must be comfortable with each step • You will be combining many elements in your applications (see figure on slide #3) • Next: • Using VB to connect to SQL Server using SP • Working with recordsets created by a SP SELECT statement

More Related