Parameters

A parameter is an argument to a stored procedure. You can optionally declare one or more parameters in a create procedure statement. The value of each parameter named in a create procedure statement must be supplied by the user when the procedure is executed.

Parameter names must be preceded by an @ sign and must conform to the rules for identifiers. Parameter names are local to the procedure that creates them; the same parameter names can be used in other procedures. Enclose any parameter value that includes punctuation (such as an object name qualified by a database name or owner name) in single or double quotes. Parameter names, including the @ sign, can be a maximum of 255 bytes long.

Parameters must be given a system datatype (except text, unitext, or image) or a user-defined datatype, and (if required for the datatype) a length or precision and scale in parentheses.

Here is a stored procedure for the pubs2 database. Given an author’s last and first names, the procedure displays the names of any books written by that person and the name of each book’s publisher.

create proc au_info @lastname varchar(40), 
  @firstname varchar(20) as 
select au_lname, au_fname, title, pub_name 
from authors, titles, publishers, titleauthor 
where au_fname = @firstname 
and au_lname = @lastname 
and authors.au_id = titleauthor.au_id 
and titles.title_id = titleauthor.title_id 
and titles.pub_id = publishers.pub_id

Now, execute au_info:

au_info Ringer, Anne

au_lname au_fname title                 pub_name 
-------- -------- --------------------- ----------
Ringer   Anne     The Gourmet Microwave Binnet & Hardley 
Ringer   Anne     Is Anger the Enemy?   New Age Books 
(2 rows affected, return status = 0)

The following stored procedure queries the system tables. Given a table name as the parameter, the procedure displays the table name, index name, and index ID:

create proc showind @table varchar(30) as 
select table_name = sysobjects.name, 
index_name = sysindexes.name, index_id = indid 
from sysindexes, sysobjects 
where sysobjects.name = @table 
and sysobjects.id = sysindexes.id

The column headings, for example, table_name, were added to improve the readability of the results. Here are acceptable syntax forms for executing this stored procedure:

execute showind titles 
exec showind titles 
execute showind @table = titles 
execute GATEWAY.pubs2.dbo.showind titles 
showind titles

The last syntax form, without exec or execute, is acceptable as long as the statement is the only one or the first one in a batch.

Here are the results of executing showind in the pubs2 database when titles is given as the parameter:

table_name  index_name  index_id 
----------  ----------  ---------- 
titles      titleidind      0 
titles      titleind        2 
 
(2 rows affected, return status = 0)

If you supply the parameters in the form “@parameter = value” you can supply them in any order. Otherwise, you must supply parameters in the order of their create procedure statement. If you supply one value in the form “@parameter = value”, then supply all subsequent parameters this way.

This procedure displays the datatype of the qty column from the salesdetail table:

create procedure showtype @tabname varchar(18), @colname varchar(18) as 
  select syscolumns.name, syscolumns.length, 
  systypes.name 
  from syscolumns, systypes, sysobjects 
  where sysobjects.id = syscolumns.id 
  and @tabname = sysobjects.name 
  and @colname = syscolumns.name 
  and syscolumns.type = systypes.type

When you execute this procedure, you can give the @tabname and @colname in a different order from the create procedure statement if you specify them by name:

exec showtype 
@colname = qty , @tabname = salesdetail

You can use case expressions in any stored procedure where you use a value expression. The following example checks the sales for any book in the titles table:

create proc booksales @titleid tid
as
select title, total_sales,
case
when total_sales != null then "Books sold"
when total_sales = null then "Book sales not available"
end
from titles
where @titleid = title_id

For example:

booksales MC2222

title                                 total_sales
------------------------              -----------
Silicon Valley Gastronomic Treats    2032 Books sold
 
(1 row affected)