Stored procedures are a set of SQL statements (one or more) typically grouped together to perform...

 

 

Transcribed Image Text:

Stored procedures are a set of SQL statements (one or more) typically grouped together to perform a specific routine. Stored procedures can be created in any user-defined database and system database except the resource database. Some of the benefits of using stored procedures are as follows: • They offer improved performance because of compiled code. They are easy to maintain because changes are central instead of inline with code. • Since database operations can be performed inside the stored procedures, they provide a strong level of security. Instead of access being granted to the underlying object, permission can be granted only to the stored procedure. Essentially, stored procedures create a level of abstraction for permissions-instead of the user being granted SELECT, INSERT, UPDATE, or DELETE rights, the user can be granted EXECUTE rights to a stored procedure. The basic syntax for creating a stored procedure is follows: CREATE PROC PROCEDURE) [schema nane.] procedure name [number] [(@parameter [type_schena_nane. J data type) [ VARYING][default ] [ OUT | OUTPUT] [READONLY] ] [...] [WITH <procedure option [....]] [FOR REPLICATION] AS ( [BEGIN] sql statement [] [] [END] ) [+] <procedure option> [ENCRYPTION] [RECOMPILE ] EXECUTE AS Clause ] Step 1 Let's create a simple store procedure that returns the Purchase OrderID, Purchase OrderDetailID, OrderDate, TotalDue, ReceivedQty and ProductName information of the products sold. CREATE PROCEDURE sp PurchaseOrderInformation AS BEGIN END SELECT poh. PurchaseOrder ID, pod.PurchaseOrderDetailID, poh.OrderDate, poh. TotalDue, pod. ReceivedQty, p.Nane ProductName FROM Purchasing. PurchaseOrderHeader poh INNER 301N Purchasing. PurchaseOrderDetail pod ON poh. Purchase order 10- pod. Purchase order ID INNER JOIN Production Product p ON pod ProductID p.productID Step 2 - To execute a stored procedure using T-SQL, you use the EXECUTE or EXEC keyword. exec sp Purchase Order Information; 1234 4 5 6 7 8 9 10 1 3 Results 4 PurchaseOrderID PurchaseOrderDetailD 1 6 2 7 2 3 4 5 6 7 7 EXEC sp PurchaseOrder Information WITH RESULT SETS Step 3 - To change the result set, you issue the EXECUTE command as normal, but you add a WITH RESULT SETS statement, and within the parentheses you provide a new column definition for each column in the result set. Messages ** Execute the query and review the results. Results | Messages Purchase Order ID Purchase Order Detail ID Order Date 1 2 3 4 1 2 4 5 6 8 7 9 7 10 7 5 6 7 8 2 3 9 10 [Purchase Order ID] int, [Purchase Order Detail ID] int, [Order Date] datetime, [Total Due] Money, [Received Quantity] float, [Product Name] varchar (50) 1 2 Order Date ReceivedOty 3.00 Total Due 2011-04-16 00:00:00.000 222,1492 2011-04-16 00:00:00.000 300.6721 3.00 2011-04-16 00:00:00.000 300.6721 3.00 2011-04-16 00:00:00.000 9776.2665 550.00 2011-04-16 00:00:00.000 189.0395 2.00 2011-04-30 00:00:00.000 22539.0165 550.00 2011-04-30 00:00:00.000 16164.0229 468.00 2011-04-30 00:00:00.000 64847.5328 550.00 2011-04-30 00:00:00.000 64847.5328 550.00 2011-04-30 00:00:00.000 64847,5328 550.00 3 4 5 6 7 8 9 10 Product Name Adjustable Race Thin-Jam Hex Nut 9 Thin-Jam Hex Nut 10 Seat Post Headset Ball Bearings HL Road Rim Touring Rim LL Crankam ML Crankarm HL Crankam Total Due 2011-04-16 00:00:00.000 222,1492 3 2011-04-16 00:00:00.000 300.6721 3 2011-04-16 00:00:00.000 300,6721 3 2011-04-16 00:00:00.000 9776.2665 550 2011-04-16 00:00:00.000 189.0395 2 2011-04-30 00:00:00.000 22539.0165 550 2011-04-30 00:00:00.000 16164.0229 468 2011-04-30 00:00:00.000 64847.5328 550 2011-04-30 00:00:00.000 64847,5328 550 2011-04-30 00:00:00.000 64847.5328 550 Received Quantity Product Name Adjustable Race Thin-Jam Hex Nut 9 Thin-Jam Hex Nut 10 Seat Post Headset Ball Bearings HL Road Rim Touring Rim LL Crankam ML Crankam HL Crankam ** In the preceding query, the EXEC keyword is used for a standard stored procedure execution However, the columns and data types have been changed to make the column names user-friendly. Step 4 - To create an output parameter for a stored procedure, you use the following syntax: parameter_name data_type OUTPUT For example, the following stored procedure finds products by model year and returns to number of products via the @product_count output parameter: CREATE PROCEDURE uspFindProduct Byflodel C AS BEGIN END 4 5 6 GEDRESSBL 9 10 ** Execute the query and review the results. DECLARE @count INT; EXEC uspFindProduct By Model @model year 2014, @product_count - @count OUTPUT; SELECT @count AS "Number of products found"; 12 @model year SMALLINT, @product_count INT OUTPUT 13 14 SELECT Name ListPrice Production Product 1 FROM WHERE 11 Charming YEAR (ModifiedDate) - @model year; SELECT @product_count - @ROUCOUNT; Resut Messages Lice Headset Ball Bearings Blade LL Crank ML Crankam HL Crankam Charing Bots Chairving Nut Crown Race Chain Stays Decal 1 15 Decal 2 Number of products found 504 Stored procedures are a set of SQL statements (one or more) typically grouped together to perform a specific routine. Stored procedures can be created in any user-defined database and system database except the resource database. Some of the benefits of using stored procedures are as follows: • They offer improved performance because of compiled code. They are easy to maintain because changes are central instead of inline with code. • Since database operations can be performed inside the stored procedures, they provide a strong level of security. Instead of access being granted to the underlying object, permission can be granted only to the stored procedure. Essentially, stored procedures create a level of abstraction for permissions-instead of the user being granted SELECT, INSERT, UPDATE, or DELETE rights, the user can be granted EXECUTE rights to a stored procedure. The basic syntax for creating a stored procedure is follows: CREATE PROC PROCEDURE) [schema nane.] procedure name [number] [(@parameter [type_schena_nane. J data type) [ VARYING][default ] [ OUT | OUTPUT] [READONLY] ] [...] [WITH <procedure option [....]] [FOR REPLICATION] AS ( [BEGIN] sql statement [] [] [END] ) [+] <procedure option> [ENCRYPTION] [RECOMPILE ] EXECUTE AS Clause ] Step 1 Let's create a simple store procedure that returns the Purchase OrderID, Purchase OrderDetailID, OrderDate, TotalDue, ReceivedQty and ProductName information of the products sold. CREATE PROCEDURE sp PurchaseOrderInformation AS BEGIN END SELECT poh. PurchaseOrder ID, pod.PurchaseOrderDetailID, poh.OrderDate, poh. TotalDue, pod. ReceivedQty, p.Nane ProductName FROM Purchasing. PurchaseOrderHeader poh INNER 301N Purchasing. PurchaseOrderDetail pod ON poh. Purchase order 10- pod. Purchase order ID INNER JOIN Production Product p ON pod ProductID p.productID Step 2 - To execute a stored procedure using T-SQL, you use the EXECUTE or EXEC keyword. exec sp Purchase Order Information; 1234 4 5 6 7 8 9 10 1 3 Results 4 PurchaseOrderID PurchaseOrderDetailD 1 6 2 7 2 3 4 5 6 7 7 EXEC sp PurchaseOrder Information WITH RESULT SETS Step 3 - To change the result set, you issue the EXECUTE command as normal, but you add a WITH RESULT SETS statement, and within the parentheses you provide a new column definition for each column in the result set. Messages ** Execute the query and review the results. Results | Messages Purchase Order ID Purchase Order Detail ID Order Date 1 2 3 4 1 2 4 5 6 8 7 9 7 10 7 5 6 7 8 2 3 9 10 [Purchase Order ID] int, [Purchase Order Detail ID] int, [Order Date] datetime, [Total Due] Money, [Received Quantity] float, [Product Name] varchar (50) 1 2 Order Date ReceivedOty 3.00 Total Due 2011-04-16 00:00:00.000 222,1492 2011-04-16 00:00:00.000 300.6721 3.00 2011-04-16 00:00:00.000 300.6721 3.00 2011-04-16 00:00:00.000 9776.2665 550.00 2011-04-16 00:00:00.000 189.0395 2.00 2011-04-30 00:00:00.000 22539.0165 550.00 2011-04-30 00:00:00.000 16164.0229 468.00 2011-04-30 00:00:00.000 64847.5328 550.00 2011-04-30 00:00:00.000 64847.5328 550.00 2011-04-30 00:00:00.000 64847,5328 550.00 3 4 5 6 7 8 9 10 Product Name Adjustable Race Thin-Jam Hex Nut 9 Thin-Jam Hex Nut 10 Seat Post Headset Ball Bearings HL Road Rim Touring Rim LL Crankam ML Crankarm HL Crankam Total Due 2011-04-16 00:00:00.000 222,1492 3 2011-04-16 00:00:00.000 300.6721 3 2011-04-16 00:00:00.000 300,6721 3 2011-04-16 00:00:00.000 9776.2665 550 2011-04-16 00:00:00.000 189.0395 2 2011-04-30 00:00:00.000 22539.0165 550 2011-04-30 00:00:00.000 16164.0229 468 2011-04-30 00:00:00.000 64847.5328 550 2011-04-30 00:00:00.000 64847,5328 550 2011-04-30 00:00:00.000 64847.5328 550 Received Quantity Product Name Adjustable Race Thin-Jam Hex Nut 9 Thin-Jam Hex Nut 10 Seat Post Headset Ball Bearings HL Road Rim Touring Rim LL Crankam ML Crankam HL Crankam ** In the preceding query, the EXEC keyword is used for a standard stored procedure execution However, the columns and data types have been changed to make the column names user-friendly. Step 4 - To create an output parameter for a stored procedure, you use the following syntax: parameter_name data_type OUTPUT For example, the following stored procedure finds products by model year and returns to number of products via the @product_count output parameter: CREATE PROCEDURE uspFindProduct Byflodel C AS BEGIN END 4 5 6 GEDRESSBL 9 10 ** Execute the query and review the results. DECLARE @count INT; EXEC uspFindProduct By Model @model year 2014, @product_count - @count OUTPUT; SELECT @count AS "Number of products found"; 12 @model year SMALLINT, @product_count INT OUTPUT 13 14 SELECT Name ListPrice Production Product 1 FROM WHERE 11 Charming YEAR (ModifiedDate) - @model year; SELECT @product_count - @ROUCOUNT; Resut Messages Lice Headset Ball Bearings Blade LL Crank ML Crankam HL Crankam Charing Bots Chairving Nut Crown Race Chain Stays Decal 1 15 Decal 2 Number of products found 504

Answer rating: 100% (QA)

It seems that the images contain information about SQL stored procedures Stored procedures are colle... View the full answer