To increase stored procedure flexibility and perform more complex processing, you can pass parameters to the procedures. The parameters can be used anywhere that local variables can be used within the procedure. For more information on how to use local variables, refer to Chapter 26.
Following is an example of a stored procedure that requires three parameters:
CREATE PROC myproc @parm1 int, @parm2 int, @parm3 int AS -- Processing goes here RETURN
To help identify the data values for which the parameters are defined, it is recommended that you give your parameters meaningful names. Parameter names, such as local variables, can be up to 128 characters in length including the @ sign, and they must follow SQL Server rules for identifiers. Up to 2,100 parameters can be defined for a stored procedure.
When you execute the procedure, you can pass the parameters by position or by name:
--Passing parameters by position EXEC myproc 1, 2, 3 --Passing parameters by name EXEC myproc @parm2 = 2, @parm2 = 1, @parm3 =3 --Passing parameters by position and name EXEC myproc 1, @parm3 =3, @parm2 = 2
After you start passing a parameter by name, you cannot pass subsequent parameters by position. All remaining parameters must be passed by name as well. If you want to skip parameters that are not the last parameter(s) in the procedure and have them take default values, you will also need to pass parameters by name.
TIPWhen embedding calls to stored procedures in client applications and script files, it is advisable to pass parameters by name. Reviewing and debugging the code becomes easier that way. I once spent half a day debugging a set of nested stored procedures only to find the problem was due to a missed parameter; everything shifted over one and the wrong values ended up in the wrong parameters, resulting in the queries not finding any matching values. That was a lesson learned the hard way! |
Input parameter values passed in can only be explicit constant values or local variables or parameters. You cannot specify a function or other expression as an input parameter value. You would have to store the function or expression value in a local variable and pass the variable as the input parameter. Likewise, you cannot use a function or other expression as a default value for a parameter.
You can assign a default value to a parameter by specifying a value in the definition of the parameter, as shown in Listing 28.9.
CREATE PROCEDURE title_authors @state char(2) = '%' AS SELECT a.au_lname, a.au_fname, t.title FROM titles t INNER JOIN titleauthor ta ON t.title_id = ta.title_id RIGHT OUTER JOIN authors a ON ta.au_id = a.au_id WHERE a.state like @state RETURN GO
You can have SQL Server apply the default value for a parameter during execution by not specifying a value or by specifying the DEFAULT keyword in the execution of the parameter, as shown in Listing 28.10.
EXEC title_authors EXEC title_authors DEFAULT EXEC title_authors @state = DEFAULT
TIPIf you are involved in creating stored procedures that other people will use, you probably want to make the stored procedures as easy to use as possible. If you leave out a parameter that is required, SQL Server presents an error message. The MyProc procedure, shown earlier in this section, requires three parameters: @parm1, @parm2, and @parm3: EXEC myproc Server: Msg 201, Level 16, State 2, Procedure myproc, Line 0 Procedure 'myproc' expects parameter '@parm1', which was not supplied. Note that SQL Server only complains about the first missing parameter. The programmer passes the first parameter, only to find out that more parameters are required. This is a good way to annoy a programmer or end user. When you execute a command-line program, you probably expect that you can use /? to obtain a list of the parameters that the program expects. You can program stored procedures in a similar manner by assigning NULL (or some other special value) as a default value to the parameters and checking for that value inside the procedure. Listing 28.11 shows an outline of a stored procedure that presents the user with information about the parameters expected if the user doesn't pass parameters. You can develop a standard for the way that the message is presented to the user, but what is important is that the information is passed. |
CREATE PROC MyProc2 @parm1 int = NULL, @parm2 int = 32, @parm3 int = NULL AS IF (@parm1 IS NULL or @parm1 NOT BETWEEN 1 and 10) OR @parm3 IS NULL PRINT 'Usage: EXEC MyProc2 @parm1 int, (Required: Can be between 1 and 10) @parm2 = 32, (Optional: Default value of 32) @parm3 int, (Required: Any number within range)' -- Processing goes here RETURN GO EXEC MyProc2 GO Usage: EXEC MyProc2 @parm1 int, (Required: Can be between 1 and 10) @parm2 = 32, (Optional: Default value of 32) @parm3 int, (Required: Any number within range)
To display the parameters defined for a stored procedure, you can view them in the Query Analyzer Object Browser (see Figure 28.5) or by executing the sp_help stored procedure as shown in Listing 28.12. (Note that the output has been edited to fit the page.)
exec sp_help title_authors Name Owner Type Created_datetime ----------------- ---------- ----------------- ------------------------------- title_authors dbo stored procedure 2001-04-15 21:15:06.540 Parameter_name Type Length Prec Scale Param_order Collation -------------- ----- ------ ---- ----- ----------- ---------------------------- @state char 2 2 NULL 1 SQL_Latin1_General_CP1_CI_AS
You can also display the stored procedure parameters by running a query against the INFORMATION_SCHEMA view parameters:
select substring(Parameter_NAME,1, 30) as Parameter_name, substring (DATA_TYPE, 1, 20) as Data_Type, CHARACTER_MAXIMUM_LENGTH as Length, ordinal_position as param_order, Collation_name from INFORMATION_SCHEMA.parameters where specific_name = 'title_authors' order by ordinal_position go Parameter_name Data_Type Length param_order Collation_name ---------------- ------------- ------- ----------- ---------------------------- @state char 2 1 SQL_Latin1_General_CP1_CI_AS
You cannot pass object names as parameters to be used in place of an object name in a stored procedure unless the object name is used as an argument in a where clause or in a dynamically built query using the EXEC statement. For example, the code in Listing 28.13 generates an odd error message when you try to create the stored procedure.
CREATE proc find_data @table varchar(128) as select * from @table GO Server: Msg 137, Level 15, State 2, Procedure find_data, Line 6 Must declare the variable '@table'.
This error seems odd because the variable @table is declared as a parameter. However, SQL Server is expecting the variable to be defined as a table variable. (Using table variables in stored procedures is discussed later in this chapter.) Listing 28.14 shows a possible approach to this problem using the EXEC() command.
CREATE proc find_data @table varchar(128) as exec ('select * from ' + @table) return go exec find_data @table = 'publishers' go pub_id pub_name city state country ------ --------------------------- --------------- ----- --------- 0736 New Moon Books Boston MA USA 0877 Binnet & Hardley Washington DC USA 1389 Algodata Infosystems Berkeley CA USA 1622 Five Lakes Publishing Chicago IL USA 1756 Ramona Publishers Dallas TX USA 9901 GGG&G München NULL Germany 9952 Scootney Books New York NY USA 9999 Lucerne Publishing Paris NULL France
Wildcards can be passed as input parameters and used in a LIKE clause in a query to perform pattern matching. Define the parameter with the varchar datatype. Do not use the char datatype because it will pad spaces onto the value passed into the parameter. For example, if you declared a @lastname parameter as char(40) and passed in 'S%', SQL Server would search not for a string starting with 'S', but for a string starting with 'S' and ending with 38 spaces. This would likely not match any data values.
Also, to increase the flexibility of a stored procedure that searches for character strings, you can default the parameter to '%', as in the following example:
create proc find_authors @lastname varchar(40) = '%' as select au_id, au_lname, au_fname from authors where au_lname like @lastname order by au_lname, au_fname
This procedure, if passed no parameter, will return data for all authors in the authors table. If passed a string containing wildcard characters, this procedure will return data for all authors matching the search pattern specified. If a string containing no wildcards is passed, the query will perform a search for exact matches against the string value.
Unfortunately, wildcard searches can only be performed against character strings. If you want to have similar flexibility searching against a numeric value, such as an integer, you can default the value to NULL and when the parameter is NULL, compare the column with itself, as shown in the following example:
create proc find_titles_by_sales @ytd_sales int = null as select title_id, title, ytd_sales from titles where ytd_sales = isnull(@ytd_sales, ytd_sales)
However, the problem with this approach is that the procedure returns all rows from the titles table except those where ytd_sales contains a NULL value. This is because NULL is never considered equal to NULL; you cannot compare an unknown value with another unknown value. To return all rows including those in which ytd_sales is NULL, you need to implement a dual query solution, as in the following example:
create proc find_titles_by_sales @ytd_sales int = null as if @ytd_sales is null select title_id, title, ytd_sales from titles else select title_id, title, ytd_sales from titles where ytd_sales= @ytd_sales