Sql server® 2012 t-sql fundamentals

tive action where relevant.  I discuss error handling later in this chapter.

www.it-ebooks.info

---

 

CHAPTER 10

 

Programmable Objects

 

 365

■

■

Stored procedures give you performance benefits.  Earlier I talked about reuse of previ-

ously cached execution plans. Stored procedures reuse execution plans by default, whereas 

SQL Server is more conservative with the reuse of ad-hoc plans. Also, the aging of procedure 

plans is less rapid than that of ad-hoc plans. Another performance benefit of using stored 

procedures is reduction of network traffic. The client application needs to submit only the 

procedure name and its arguments to SQL Server. The server processes all of the procedure’s 

code and returns only the output back to the caller. No back-and-forth traffic is associated 

with intermediate steps of the procedure.

As a simple example, the following code creates a stored procedure called Sales.GetCustomer-

Orders. The procedure accepts a customer ID (@custid) and a date range (@fromdate and @todate) 

as inputs. The 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).

IF OBJECT_ID('Sales.GetCustomerOrders', 'P') IS NOT NULL 

  DROP PROC Sales.GetCustomerOrders; 

GO 

 

CREATE PROC Sales.GetCustomerOrders 

  @custid   AS INT, 

  @fromdate AS DATETIME = '19000101', 

  @todate   AS DATETIME = '99991231', 

  @numrows  AS INT OUTPUT 

AS 

SET NOCOUNT ON; 

 

SELECT orderid, custid, empid, orderdate 

FROM Sales.Orders 

WHERE custid = @custid 

  AND orderdate >= @fromdate 

  AND orderdate < @todate; 

 

SET @numrows = @@rowcount; 

GO

When executing the procedure, if you don’t specify a value in the @fromdate parameter, the pro-

cedure will use the default 19000101, and if you don’t specify a value in the @todate parameter, the 

procedure will use the default 99991231. Notice the use of the keyword OUTPUT to indicate 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, such as the SELECT statement 

within the procedure.

Here’s an example of executing the procedure, requesting information about orders placed by the 

customer with the ID of 1 in the year 2007. The code absorbs the value of the output parameter @

numrows in the local variable @rc and returns it to show how many rows were affected by the query.

DECLARE @rc AS INT; 

 

EXEC Sales.GetCustomerOrders 

  @custid   = 1,  

www.it-ebooks.info

---

366  

Microsoft SQL Server 2012 T-SQL Fundamentals

  @fromdate = '20070101', 

  @todate   = '20080101', 

  @numrows  = @rc OUTPUT; 

 

SELECT @rc AS numrows;

The code returns the following output showing three qualifying orders.

orderid     custid      empid       orderdate 

----------- ----------- ----------- ----------------------- 

10643       1           6           2007-08-25 00:00:00.000 

10692       1           4           2007-10-03 00:00:00.000 

10702       1           4           2007-10-13 00:00:00.000 

 

numrows 

----------- 

3

Run the code again, providing a customer ID that doesn’t exist in the Orders table (for example, 

customer ID 100). You get the following output indicating that there are zero qualifying orders.

orderid     custid      empid       orderdate 

----------- ----------- ----------- ----------------------- 

 

numrows 

----------- 

0

Of course, this is just a basic example. You can do much more with stored procedures.