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.
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
exec showtype @colname = qty , @tabname = salesdetail
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
booksales MC2222
title total_sales ------------------------ ----------- Silicon Valley Gastronomic Treats 2032 Books sold (1 row affected)