The Gurus Guide to SQL Server Stored Procedures, XML, and HTML [Electronic resources] نسخه متنی

Parameters

Parameters can be passed to stored procedures by name or by position. An example of each method is presented in Listing 1-23:

Listing 1-23 You can pass procedure parameters by position or by name.

EXEC dbo.sp_who 'sa'
EXEC dbo.sp_who @loginame='sa'

Obviously, the advantage of referencing parameters by name is that you can specify them out of order.

You can force a parameter for which a default value has been defined to use that default by omitting it altogether or by passing it the DEFAULT keyword, as in Listing 1-24:

Listing 1-24 Passing DEFAULT for a parameter causes it to assume its default value.

EXEC dbo.sp_who @loginame=DEFAULT

You can specify NULL to supply individual parameters with NULL values. This is sometimes handy for procedures that expose special features when parameters are omitted or set to NULL. An example is presented in Listing 1-25:

Listing 1-25 You can pass NULL to a parameter.

EXEC dbo.sp_who @loginame=NULL

(Results abridged)

spid   ecid   status                         loginame
------ ------ ------------------------------ -----------
1      0      background                     sa
2      0      background                     sa
3      0      sleeping                       sa
4      0      background                     sa
5      0      background                     sa
6      0      sleeping                       sa
7      0      background                     sa
8      0      background                     sa
9      0      background                     sa
10     0      background                     sa
11     0      background                     sa
12     0      background                     sa
13     0      background                     sa
51     0      sleeping                       SKREWYTHIN\ khen
52     0      sleeping                       SKREWYTHIN\ khen
53     0      sleeping                       SKREWYTHIN\ khen

Here, sp_who returns a list of all active connections because its @loginame parameter is passed NULL. When a valid login name is specified, sp_who returns only those connections established by the specified login name. You'd see the same result if @loginame had not been supplied at allall connections would be listed.

Return Status Codes

Procedures return status codes via the RETURN command. For an example, see Listing 1-26:

Listing 1-26 Use RETURN to render stored procedure status codes.

RETURN(-100)
-- and
RETURN 100

These return a status code of 100 to the caller of the procedure (the parameters are optional). A return code of 0 indicates success, values 1 through 14 indicate different types of failures (see the Books Online for descriptions of these), and values 15 through 99 are reserved for future use.

You can access a procedure's return code by assigning it to an integer variable, as in Listing 1-27:

Listing 1-27 You can save a procedure's return status code to an integer variable.

DECLARE @res int
EXEC @res=dbo.sp_who
SELECT @res

Output Parameters

In addition to the return status code that every stored procedure supports, you can use output parameters to return other types of values from a procedure. These parameters can be integers, character strings, dates, and even cursors. An example is provided in Listing 1-28:

Listing 1-28 Cursor output parameters are handy for returning result sets.

USE pubs
IF OBJECT_ID('dbo.listsales') IS NOT NULL
DROP PROC dbo.listsales
GO
CREATE PROC dbo.listsales @bestseller tid OUT, @topsales int OUT,
@salescursor cursor varying OUT
AS
SELECT @bestseller=bestseller, @topsales=totalsales
FROM (
SELECT TOP 1 title_id AS bestseller, SUM(qty) AS totalsales
FROM sales
GROUP BY title_id
ORDER BY 2 DESC) bestsellers
DECLARE s CURSOR
LOCAL
FOR SELECT * FROM sales
OPEN s
SET @salescursor=s
RETURN(0)
GO
DECLARE @topsales int, @bestseller tid, @salescursor cursor
EXEC dbo.listsales @bestseller OUT, @topsales OUT, @salescursor OUT
SELECT @bestseller, @topsales
FETCH @salescursor
CLOSE @salescursor
DEALLOCATE @salescursor

(Results abridged)

------ -----------
PS2091 108
stor_id ord_num   ord_date   qty  payterms  title_id
------- --------- ---------- ---- --------- --------
6380    6871      1994-09-14 5    Net 60    BU1032

Using a cursor output parameter is a good alternative for returning a result set to a caller. By using a cursor output parameter rather than a traditional result set, you give the caller control over how and when to process the result set. The caller can also determine various details about the cursor through system function calls before actually processing the result.

Output parameters are identified with the OUTPUT keyword (you can abbreviate this as "OUT"). Note the use of the OUT keyword in the procedure definition as well as in the EXEC parameter list. Output parameters must be identified in a procedure's parameter list as well as when the procedure is called.

The VARYING keyword is required for cursor parameters and indicates that the return value is nonscalarthat is, it can return more than one value. Cursor parameters can be output parameters only, so the OUT keyword is also required.

Listing Procedure Parameters

You can list a procedure's parameters (which include its return status code, considered parameter 0) by querying the INFORMATION_SCHEMA.PARAMETERS view (Listing 1-29).

Listing 1-29 INFORMATION_SCHEMA.PARAMETERS returns stored procedure parameter info.

USE Northwind
SELECT PARAMETER_MODE, PARAMETER_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_NAME='Employee Sales by Country'

(Results abridged)

PARAMETER_MODE PARAMETER_NAME   DATA_TYPE
-------------- ---------------- ----------
IN             @Beginning_Date  datetime
IN             @Ending_Date     datetime

General Parameter Notes

In addition to what I've already said about parameters, here are a few more tips:

Check stored procedure parameters for invalid values early on.

Human-friendly names allow parameters to be passed by name more easily.

It's a good idea to provide default values for parameters when you can. This makes a procedure easier to use. A parameter default can consist of a constant or the NULL value.

Because parameter names are local to stored procedures, you can use the same name in multiple procedures. If you have ten procedures that each take a user name parameter, name the parameter @UserName in all ten of themfor simplicity's sake and for general consistency in your code.

Procedure parameter information is stored in the syscolumns system table.

A stored procedure can receive as many as 1,024 parameters. If you have a procedure that you think needs more parameters than 1,024, you should probably consider redesigning it.

The number and size of stored procedure local variables is limited only by the amount of memory available to SQL Server.

Automatic Variables, a.k.a. System Functions

By their very nature, automatic variables, also known as system functions, are usually the province of stored procedures. This makes most of them germane in some way to a discussion about stored procedures. Several, in fact, are used almost exclusively in stored procedures. Table 1-1 summarizes them.