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.
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.
Setting Default Values for Parameters
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.
Listing 28.9 Assigning a Default Value for a Parameter in a Stored Procedure
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.
Listing 28.10 Applying a Default Value for a Parameter When Executing a Stored Procedure
EXEC title_authors EXEC title_authors DEFAULT EXEC title_authors @state = DEFAULT
Listing 28.11 Presenting Information to the User About Missing Parameters for a Stored Procedure
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.)
Figure 28.5. Displaying stored procedure parameters in Query Analyzer.
Listing 28.12 Displaying Stored Procedure Parameters Using sp_help
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
Passing Object Names As Parameters
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.
Listing 28.13 Attempting to Create a Stored Procedure Using a Parameter to Pass in a Table Name
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.
Listing 28.14 Passing a Table as a Parameter to a Stored Procedure for Dynamic Query Execution
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 Mnchen NULL Germany 9952 Scootney Books New York NY USA 9999 Lucerne Publishing Paris NULL France
Using Wildcards in Parameters
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