(INCLUDE SYNTAX WITH EVERYTHING)

(INCLUDE NOTES FROM Advanced SQL book, PRAGMATIC WORKS INTRO AND ADVANCED, DATABASESTAR ACADEMY, SQL COOKBOOK, Udemy, WENZEL)

(COMPARE WITH ADVANCED SQL BOOK ONE LAST TIME)

Stored procedures

A stored procedure is code that can have input and output parameters, and they return the result sets of queries. Stored procedures are allowed to have side effects, meaning we can use stored procedures to modify data, as well as apply schema changes through them.

The following are the benefits of stored procedures over ad-hoc queries:

• Stored procedures encapsulate logic. If the implementation of a stored procedure needs to be changed, we can apply the change in one place using the ALTER PROC command. All users of the stored procedure will be using the updated stored procedure from that point.
• Stored procedures give us better control of security. We can grant user permissions to execute the procedure without granting the user direct permissions to perform the underlying activities. This ensures all required validations and auditing always takes place. In addition, stored procedures with parameters can help prevent SQL injection, especially when they’re replacing ad-hoc SQL queries submitted from a client application.
• We can incorporate all error-handling code within a procedure, silently taking corrective action when needed.
• Stored procedures provides us with performance benefits. Queries in stored procedures are usually parameterized and therefore have a high likelihood of reusing previously cached plans. Another performance benefit here is the reduction in network traffic. The client application needs to submit only the procedure name and its argument to SQL Server. SQL Server processes all the procedure’s code and returns only the output back to the caller. There is no back-and-forth traffic associated with the intermediate steps of the procedure.

For example, the following code creates a stored procedure called Sales.GetCustomerOrders, which accepts as inputs a customer ID (@custid) and a date range (@fromdate and @todate). This stored procedure returns rows from the Sales.Orders table representing orders placed by the requested customer in the requested date range as a result set, and the number of affected rows as an output parameter (@numrows):

DROP PROC IF EXISTS Sales.GetCustomerOrders;
GO

CREATE PROC Sales.GetCustomerOrders 
  @custid AS INT
  @fromdate AS DATETIME ='19000101' 
  @todfate AS DATETIME = '99991231' 
  @numrows AS INT OUTPUT 
AS 
SET NOCOUNT ON; 

SELECT SalesOrderID, 
       CustomerID, 
       SalesPersonID, 
       OrderDate 
FROM Sales.SalesOrderHeader 
WHERE customerid = @custid 
  AND orderdate >= @fromdate 
  AND orderdate < @todate; 
   
SET @numrows = @@rowcount;
GO

If the @fromdate parameter is not specified, the stored procedure will default to 19000101 when it’s executed, and if the @todate parameter is not specified then the stored procedure will use the default 99991231. The OUTPUT keyword is used to specify that the parameter @numrows is an output parameter. The SET NOCOUNT ON command is used to suppress messages indicating how many rows were affected by DML statements within the procedure (such as the SELECT statement).

The following code shows how we can execute the stored procedure. It queries for order information placed by the customer with the ID of 1 in the year 2015. The code takes the value of the output parameter @numrows into the local variable @rc and returns it to show how may rows were affected by the query:

DECLARE @rc AS INT;

EXEC Sales.GetCustomerOrders
  @custid = 1,
  @fromdate = '20150101'
  @todate = '20160101'
  @numrows = @rc OUTPUT

SELECT @rc AS numrows;

If we run the code again by providing a customer ID that doesn’t exist in the Orders table (for example, customer 100), we get the following output indicating that there are zero qualifying orders:

(Include results from page 407)