Using Input Parameters

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.

TIP

When 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.

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 

TIP

If 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.

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