CREATE PROCEDURE

Permissions

http://msdn.microsoft.com/en-us/library/aa258259(v=sql.80).aspx

CREATE PROCEDURE permissions default to members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles. Members of the sysadmin fixed server role and the db_owner fixed database role can transfer CREATE PROCEDURE permissions to other users. Permission to execute a stored procedure is given to the procedure owner, who can then set execution permission for other database users.
Examples
A. Use a simple procedure with a complex SELECT

This stored procedure returns all authors (first and last names supplied), their titles, and their publishers from a four-table join. This stored procedure does not use any parameters.
USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info_all' AND type = 'P') DROP PROCEDURE au_info_all GO CREATE PROCEDURE au_info_all AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id GO

The au_info_all stored procedure can be executed in these ways:

EXECUTE au_info_all -- Or EXEC au_info_all

Or, if this procedure is the first statement within the batch:

au_info_all
B. Use a simple procedure with parameters

This stored procedure returns only the specified authors (first and last names supplied), their titles, and their publishers from a four-table join. This stored procedure accepts exact matches for the parameters passed.
USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info' AND type = 'P') DROP PROCEDURE au_info GO USE pubs GO CREATE PROCEDURE au_info @lastname varchar(40), @firstname varchar(20) AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname = @firstname AND au_lname = @lastname GO

The au_info stored procedure can be executed in these ways:

EXECUTE au_info 'Dull', 'Ann' -- Or EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann' -- Or EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull' O

1 5

10/6/2554 2:27

CREATE PROCEDURE
-- Or EXEC au_info 'Dull', 'Ann' -- Or EXEC au_info @lastname = 'Dull', @firstname = 'Ann' -- Or EXEC au_info @firstname = 'Ann', @lastname = 'Dull'

http://msdn.microsoft.com/en-us/library/aa258259(v=sql.80).aspx

Or, if this procedure is the first statement within the batch:

au_info 'Dull', 'Ann' -- Or au_info @lastname = 'Dull', @firstname = 'Ann' -- Or au_info @firstname = 'Ann', @lastname = 'Dull'
C. Use a simple procedure with wildcard parameters

This stored procedure returns only the specified authors (first and last names supplied), their titles, and their publishers from a four-table join. This stored procedure pattern matches the parameters passed or, if not supplied, uses the preset defaults.
USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info2' AND type = 'P') DROP PROCEDURE au_info2 GO USE pubs GO CREATE PROCEDURE au_info2 @lastname varchar(30) = 'D%', @firstname varchar(18) = '%' AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname LIKE @firstname AND au_lname LIKE @lastname GO

The au_info2 stored procedure can be executed in many combinations. Only a few combinations are shown here:
EXECUTE -- Or EXECUTE -- Or EXECUTE -- Or EXECUTE -- Or EXECUTE -- Or EXECUTE au_info2 au_info2 'Wh%' au_info2 @firstname = 'A%' au_info2 '[CK]ars[OE]n' au_info2 'Hunter', 'Sheryl' au_info2 'H%', 'S%'

D. Use OUTPUT parameters

OUTPUT parameters allow an external procedure, a batch, or more than one Transact-SQL statements to access a value set during the procedure execution. In this example, a stored procedure (titles_sum) is created and allows one optional input parameter and one output parameter. First, create the procedure:
USE pubs GO IF EXISTS(SELECT name FROM sysobjects WHERE name = 'titles_sum' AND type = 'P') DROP PROCEDURE titles_sum GO USE pubs GO CREATE PROCEDURE titles_sum @TITLE varchar(40) = '%', @SUM money OUTPUT AS

2 5

10/6/2554 2:27

CREATE PROCEDURE
AS SELECT 'Title Name' = title FROM titles WHERE title LIKE @TITLE SELECT @SUM = SUM(price) FROM titles WHERE title LIKE @TITLE GO

http://msdn.microsoft.com/en-us/library/aa258259(v=sql.80).aspx

Next, use the OUTPUT parameter with control-of-flow language. Note The OUTPUT variable must be defined during the table creation as well as during use of the variable. The parameter name and variable name do not have to match; however, the data type and parameter positioning must match (unless @SUM = variable is used).
DECLARE @TOTALCOST money EXECUTE titles_sum 'The%', @TOTALCOST OUTPUT IF @TOTALCOST < 200 BEGIN PRINT ' ' PRINT 'All of these titles can be purchased for less than $200.' END ELSE SELECT 'The total cost of these titles is $' + RTRIM(CAST(@TOTALCOST AS varchar(20)))

Here is the result set:

Title Name -----------------------------------------------------------------------The Busy Executive's Database Guide The Gourmet Microwave The Psychology of Computer Cooking (3 row(s) affected) Warning, null value eliminated from aggregate. All of these titles can be purchased for less than $200.
E. Use an OUTPUT cursor parameter

OUTPUT cursor parameters are used to pass a cursor that is local to a stored procedure back to the calling batch, stored procedure, or trigger. First, create the procedure that declares and then opens a cursor on the titles table:
USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'titles_cursor' and type = 'P') DROP PROCEDURE titles_cursor GO CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT AS SET @titles_cursor = CURSOR FORWARD_ONLY STATIC FOR SELECT * FROM titles OPEN @titles_cursor GO

Next, execute a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the cursor.
USE pubs GO DECLARE @MyCursor CURSOR EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT WHILE (@@FETCH_STATUS = 0) BEGIN FETCH NEXT FROM @MyCursor END CLOSE @MyCursor

3 5

10/6/2554 2:27

CREATE PROCEDURE
CLOSE @MyCursor DEALLOCATE @MyCursor GO
F. Use the WITH RECOMPILE option

http://msdn.microsoft.com/en-us/library/aa258259(v=sql.80).aspx

The WITH RECOMPILE clause is helpful when the parameters supplied to the procedure will not be typical, and when a new execution plan should not be cached or stored in memory.
USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'titles_by_author' AND type = 'P') DROP PROCEDURE titles_by_author GO CREATE PROCEDURE titles_by_author @LNAME_PATTERN varchar(30) = '%' WITH RECOMPILE AS SELECT RTRIM(au_fname) + ' ' + RTRIM(au_lname) AS 'Authors full name', title AS Title FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON ta.title_id = t.title_id WHERE au_lname LIKE @LNAME_PATTERN GO
G. Use the WITH ENCRYPTION option

The WITH ENCRYPTION clause obfuscates the text of a stored procedure. This example creates an obfuscated procedure, uses the sp_helptext system stored procedure to get information on that obfuscated procedure, and then attempts to get information on that procedure directly from the syscomments table.
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'encrypt_this' AND type = 'P') DROP PROCEDURE encrypt_this GO USE pubs GO CREATE PROCEDURE encrypt_this WITH ENCRYPTION AS SELECT * FROM authors GO EXEC sp_helptext encrypt_this

Here is the result set:

The object's comments have been encrypted.

Next, select the identification number and text of the obfuscated stored procedure contents.
SELECT c.id, c.text FROM syscomments c INNER JOIN sysobjects o ON c.id = o.id WHERE o.name = 'encrypt_this'

Here is the result set: Note The text column output is shown on a separate line. When executed, this information appears on the same line as the id column information.
id text ---------- -----------------------------------------------------------1413580074 ?????????????????????????????????e?????????????????????????????????????????????????????????????????????????? (1 row(s) affected)
H. Create a user-defined system stored procedure

This example creates a procedure to display all the tables and their corresponding indexes with a table name beginning with the string emp If not specified this procedure returns all tables (and

4 5

10/6/2554 2:27

CREATE PROCEDURE

http://msdn.microsoft.com/en-us/library/aa258259(v=sql.80).aspx

table name beginning with the string emp. If not specified, this procedure returns all tables (and indexes) with a table name beginning with sys.
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_showindexes' AND type = 'P') DROP PROCEDURE sp_showindexes GO USE master GO CREATE PROCEDURE sp_showindexes @TABLE varchar(30) = 'sys%' AS SELECT o.name AS TABLE_NAME, i.name AS INDEX_NAME, indid AS INDEX_ID FROM sysindexes i INNER JOIN sysobjects o ON o.id = i.id WHERE o.name LIKE @TABLE GO USE pubs EXEC sp_showindexes 'emp%' GO

Here is the result set:

TABLE_NAME INDEX_NAME INDEX_ID ---------------- ---------------- ---------------employee employee_ind 1 employee PK_emp_id 2 (2 row(s) affected)
I. Use deferred name resolution

This example shows a procedure that uses deferred name resolution. The stored procedure is created although the table that is referenced does not exist at compile time. The table must exist, however, at the time the procedure is executed.
USE master GO IF EXISTS (SELECT name FROM sysobjects WHERE name = 'proc1' AND type = 'P') DROP PROCEDURE proc1 GO -- Create a procedure on a nonexistent table. USE pubs GO CREATE PROCEDURE proc1 AS SELECT * FROM does_not_exist GO -- This statement returns the text of the procedure proc1. SELECT o.id, c.text FROM sysobjects o INNER JOIN syscomments c ON o.id = c.id WHERE o.type = 'P' AND o.name = 'proc1' GO

5 5

10/6/2554 2:27