You can define input parameters for a stored procedure in its header. An input parameter must be provided with a value when the stored procedure is invoked unless you assign the parameter with a default value. As an example, the following code creates the usp_GetCustOrders procedure, which accepts a customer ID and datetime range boundaries as inputs, and returns the given customer’s orders in the given datetime range:

USE Northwind;
GO

IF OBJECT_ID('dbo.usp_GetCustOrders') IS NOT NULL
  DROP PROC dbo.usp_GetCustOrders;
GO

CREATE PROC dbo.usp_GetCustOrders
  @custid   AS NCHAR(5),
  @fromdate AS DATETIME = '19000101',
  @todate   AS DATETIME = '99991231'
AS

SET NOCOUNT ON;

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE CustomerID = @custid
  AND OrderDate >= @fromdate
  AND OrderDate < @todate;
GO

Tip

The SET NOCOUNT ON option tells SQL Server not to produce the message saying how many rows were affected for data manipulation language (DML) statements. Some client database interfaces, such as OLEDB, absorb this message as a row set. The result is that when you expect to get a result set of a query back to the client, instead you get this message of how many rows were affected as the first result set. By issuing SET NOCOUNT ON, you avoid this problem in those interfaces, so you might want to adopt the practice of specifying it.

When invoking a stored procedure, you must specify inputs for those parameters that were not given default values in the definition (for @custid in our case). There are two formats for assigning values to parameters when invoking a stored procedure: unnamed and named. In the unnamed format, you just specify values without specifying the parameter names. Also, you must specify the inputs by declaration order of the parameters. You can omit inputs only for parameters that have default values and that were declared at the end of the parameter list. You cannot omit an input between two parameters for which you do specify values. If you want such parameters to use their default values, you would need to specify the DEFAULT keyword for those.

As an example, the following code invokes the procedure without specifying the inputs for the two last parameters, which will use their default values, and produces the output shown in Table 7-3:

EXEC dbo.usp_GetCustOrders N'ALFKI';

If you want to specify your own value for the third parameter but use the default for the second, specify the DEFAULT keyword for the second parameter:

EXEC dbo.usp_GetCustOrders N'ALFKI', DEFAULT, '20060212';

This code also produces the output in Table 7-3.

And, of course, if you want to specify your own values for all parameters, just specify them in order, as in:

EXEC dbo.usp_GetCustOrders N'ALFKI', '19970101', '19980101';

which produces the output shown in Table 7-4:

These are the basics of stored procedures. You’re probably already familiar with them, but I decided to include this coverage to lead to a recommended practice. There are many maintenance-related issues that can arise when using the unnamed assignment format. You must specify the arguments in order; you must not omit an optional parameter; and by looking at the code, it might not be clear what the inputs actually mean and to which parameter they relate. Therefore, it’s a good practice to use the named assignment format, where you specify the name of the argument and assign it with an input value, as in:

EXEC dbo.usp_GetCustOrders
  @custid   = N'ALFKI',
  @fromdate = '19970101',
  @todate   = '19980101';

The code is much more readable; you can play with the order in which you specify the inputs; and you can omit any parameter that you like if it has a default value.

Output parameters allow you to return output values from a stored procedure. A change made to the output parameter within the stored procedure is reflected in the variable from the calling batch that was assigned to the output parameter. The concept is similar to a pointer in C or a ByRef parameter in Visual Basic.

As an example, the following code alters the definition of the usp_GetCustOrders procedure, adding to it the output parameter @numrows:

ALTER PROC dbo.usp_GetCustOrders
  @custid   AS NCHAR(5),
  @fromdate AS DATETIME = '19000101',
  @todate   AS DATETIME = '99991231',
  @numrows  AS INT OUTPUT
AS

SET NOCOUNT ON;
DECLARE @err AS INT;

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE CustomerID = @custid
  AND OrderDate >= @fromdate
  AND OrderDate < @todate;

SELECT @numrows = @@rowcount, @err = @@error;

RETURN @err;
GO

@numrows will return the number of rows affected by the query. Notice that the stored procedure also uses a RETURN clause to return the value of the @@error function after the invocation of the query.

To get the output parameter back from the stored procedure when invoking it, you will need to assign it with a variable defined in the calling batch and mention the keyword OUTPUT. To get back the return status, you will also need to provide a variable from the calling batch right before the procedure name and an equal sign. Here’s an example:

DECLARE @myerr AS INT, @mynumrows AS INT;

EXEC @myerr = dbo.usp_GetCustOrders
  @custid   = N'ALFKI',
  @fromdate = '19970101',
  @todate   = '19980101',
  @numrows  = @mynumrows OUTPUT;

SELECT @myerr AS err, @mynumrows AS rc;

The stored procedure returns the output shown in Table 7-4, plus it assigns the return status 0 to @myerr and the number of affected rows (in this case, 3) to the @mynumrows variable.

If you want to manipulate the row set returned by the stored procedure with T-SQL, you will need to create a table first and use the INSERT/EXEC syntax, as shown in Example 7-2.

IF OBJECT_ID('tempdb..#CustOrders') IS NOT NULL
  DROP TABLE #CustOrders;
GO
CREATE TABLE #CustOrders
(
  OrderID    INT      NOT NULL PRIMARY KEY,
  CustomerID NCHAR(5) NOT NULL,
  EmployeeID INT      NOT NULL,
  OrderDate  DATETIME NOT NULL
);

DECLARE @myerr AS INT, @mynumrows AS INT;

INSERT INTO #CustOrders(OrderID, CustomerID, EmployeeID, OrderDate)
  EXEC @myerr = dbo.usp_GetCustOrders
    @custid   = N'ALFKI',
    @fromdate = '19970101',
    @todate   = '19980101',
    @numrows  = @mynumrows OUTPUT;

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM #CustOrders;

SELECT @myerr AS err, @mynumrows AS rc;
GO

A client will accept output from a stored procedure into client objects. For example, in ADO programming you define items in a Parameters collection for input parameters, output parameters, and return status. A stored procedure can return more than one result set if within it you invoke multiple queries. In the client code, you will absorb the result sets, moving from one record set to another–for example, using the .NextRecordset property of the Recordset object in ADO. A stored procedure can generate other types of outputs as well, including the output of PRINT and RAISERROR commands. Both would be received by the client through the client interface’s structures–for example, the Errors collection in ADO.

ADO.NET allows you to accept any possible output from a SQL Server stored procedure at the client side. Of course, in order to accept some output, as the first step, you have to execute the procedure. You execute a stored procedure by using the SqlCommand object. To denote you are executing a stored procedure, you have to set the CommandType property of the SqlCommand object to CommandType.StoredProcedure. To define which procedure to execute, you have to insert the name of the procedure to the CommandText property. To actually execute the procedure, use the ExecuteScalar, ExecuteNonQuery, ExecuteReader, or ExecuteXmlReader methods of the SqlCommand object, depending on the output(s) of the stored procedure. Following are the different types of output of stored procedures needed to get the output:

For examples and more details about ADO.NET, please refer to ADO.NET Examples and Best Practices for C# Programmers (Apress, 2002) by William R. Vaughn and Peter Blackburn.

When you’re done, run the following code for cleanup:

USE Northwind;
GO
IF OBJECT_ID('dbo.usp_GetCustOrders') IS NOT NULL
  DROP PROC dbo.usp_GetCustOrders;
GO
IF OBJECT_ID('tempdb..#CustOrders') IS NOT NULL
  DROP TABLE #CustOrders;
GO