This chapter is from the book
This chapter is from the book
This chapter is from the book
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 all—all 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 nonscalar—that 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 them—for 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.
Table 1–1 Stored Procedure-Related Functions
|
Function |
Returns |
|
@@FETCH_STATUS |
The status of the last FETCH operation |
|
@@NESTLEVEL |
The current procedure nesting level |
|
@@OPTIONS |
A bitmap of the currently specified user options |
|
@@PROCID |
The object ID of the current procedure |
|
@@SPID |
The process ID of the current process |
|
@@TRANCOUNT |
The current transaction nesting level |