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:

 @parm1 int, @parm2 int, @parm3 int
-- Processing goes here

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.


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) = '%'
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

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


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

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
 @parm1 int = NULL, @parm2 int = 32, @parm3 int = NULL
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

EXEC MyProc2

 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,
       ordinal_position as param_order,
from INFORMATION_SCHEMA.parameters
where specific_name = 'title_authors'
order by ordinal_position


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)

select * from @table


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)

exec ('select * from ' + @table)
exec find_data @table = 'publishers'

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

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) = '%' 
    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 
    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 
if @ytd_sales is null
    select title_id, title, ytd_sales
        from titles
    select title_id, title, ytd_sales
        from titles
        where ytd_sales= @ytd_sales

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features