Stored procedures are a set of SQL statements (one or more) typically grouped together to perform...
Fantastic news! We've Found the answer you've been seeking!
Question:
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
Expert Answer:
Answer rating: 100% (QA)
It seems that the images contain information about SQL stored procedures Stored procedures are colle... View the full answer
Related Book For
Accounting Information Systems
ISBN: 9780132871938
11th Edition
Authors: George H. Bodnar, William S. Hopwood
Posted Date:
Students also viewed these databases questions
-
Solve the linear programming problem by the method of corners. (There may be more than one correct answer.) C=5x+by Minimize subject to 4x + y 42 2x+ y 30 x+3y 2 30 x20, y 20 The minimum is C 52.5...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Googles ease of use and superior search results have propelled the search engine to its num- ber one status, ousting the early dominance of competitors such as WebCrawler and Infos- eek. Even later...
-
Assume that you have been hired as a consultant by CGT, a major producer of chemicals and plastics, including plastic grocery bags, styrofoam cups, and fertilizers, to estimate the firms weighted...
-
How much heat is needed to melt 23.50 kg of silver that is initially at 25C?
-
How do individuals and firms generally make decisions concerning economic situations?
-
Manchester Co. operates as a manufacturer of industrial equipment. On March 28, 2020, Manchester Co. required an advance payment of \(\$ 100,000\) from a customer on a special order of equipment. On...
-
Central Duplicating Internal Service Fund. As of September 30, 2010, the Central Duplicating Fund of the Town of Fredericksburg had the following post-closing trial balance: During the fiscal year...
-
Project T points Flash Electronics Company produces a variety of electronic equipment. One of the plants produces two laser printers: the de have and the regular. The demand for the printers is 1 000...
-
A mail-order catalog business that sells personal computer supplies, software, and hardware maintains a centralized warehouse for the distribution of products ordered. Management is currently...
-
As you have explored, ongoing challenges in the global business environment are mostly attributed to unethical business practices, failure to embrace technology advancements, and stiff competition...
-
Solve the following problem by using MATLAB given th 1-27 2-10 x-30 Find I Add file
-
Think about current political movements or campaigns connected to specific geographies. What kinds of economic discussions might be taking place during the upcoming primary and general elections for...
-
2. (10) In a few sentences, describe what the script below is doing (FYI: GE means greater than or equal). Be as specific as possible. UMULL RO, R1, R2, R3 CMP R1, # 0x01 BGE Elsewhere
-
An all-equity firm is considering the following projects: Project Beta IRR W .80 10.2% .90 1.10 1.35 X Y Z 11.4 12.6 15.1 The T-bill rate is 4 percent, and the expected return on the market is 12...
-
Holly Homeowner was a homeowner no more. Due to a protracted illness, she was unable to work. When her disability benefits ran out, she was unable to make her mortgage payment. Subsequently, she lost...
-
Kate plans to save $ 1 0 0 a month, starting today, for 2 0 years. Jane plans to save $ 1 0 0 a month for 2 0 years, starting one month from today. Both Kate and Jane expect to earn an average return...
-
White Bolder Investments (WBI) You are an intern working for WBI, a large investment advisory services in Sydney. Among other regular customers, WBI has been providing advisory services for Jumbo...
-
What are the objectives of a fixed- asset or investment accounting system?
-
What is a virus program? Give several examples.
-
You decided to use a newly acquired audit software package in auditing accounts payable. The accounts payable system has been computerized for several years, and the transaction records are recorded...
-
12. ROLE REVERSAL: Write a multiple-choice question that raises the issue of an instrument's negotiability.
-
Question: Winona owns a tropical fish store. To buy a spectacular new tank, she borrows $25,000 from her sister, Pauline, and signs an agreement giving Pauline a security interest in the tank....
-
Consider the set of vectors \((-1,1,1),(1,-1,1),(1,1,-1)\). a. Use the Gram-Schmidt process to find an orthonormal basis for \(R^{3}\) using this set in the given order. b. What do you get if you do...
Study smarter with the SolutionInn App