Shabir has 12 yrs of exp in I.T using Asp, Asp.NET, Vb6/.NET/C#, MOSS/WMI/html/DOM, Ajax, XML,xsl.SharePoint
You can create stored procedures using the CREATE PROCEDURE Transact-SQL statement. Before creating a stored procedure, consider that:
When creating a stored procedure, you should specify:
Many of your administrative activities in Microsoft® SQL Server™ 2000 are performed through a special kind of procedure known as a system stored procedure. System stored procedures are created and stored in the master database and have the sp_ prefix. System stored procedures can be executed from any database without having to qualify the stored procedure name fully using the database name master.
It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:
Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name.
Important If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.
A procedure can be created with the same name as an existing stored procedure if it is given a different identification number, which allows the procedures to be grouped logically. Grouping procedures with the same name allows them to be deleted at the same time. Procedures used in the same application are often grouped this way. For example, the procedures used with the my_app application might be named my_proc;1, my_proc;2, and so on. Deleting my_proc deletes the entire group. After procedures have been grouped, individual procedures within the group cannot be deleted.
Private and global temporary stored procedures, analogous to temporary tables, can be created with the # and ## prefixes added to the procedure name. # denotes a local temporary stored procedure; ## denotes a global temporary stored procedure. These procedures do not exist after SQL Server is shut down.
Temporary stored procedures are useful when connecting to earlier versions of SQL Server that do not support the reuse of execution plans for Transact-SQL statements or batches. Applications connecting to SQL Server version 2000 should use the sp_executesql system stored procedure instead of temporary stored procedures. For more information, see Execution Plan Caching and Reuse.
Only the connection that created a local temporary procedure can execute it, and the procedure is automatically deleted when the connection is closed (when the user logs out of SQL Server).
Any connection can execute a global temporary stored procedure. A global temporary stored procedure exists until the connection used by the user who created the procedure is closed and any currently executing versions of the procedure by any other connections are completed. Once the connection that was used to create the procedure is closed, no further execution of the global temporary stored procedure is allowed. Only those connections that have already started executing the stored procedure are allowed to complete.
If a stored procedure not prefixed with # or ## is created directly in the tempdb database, the stored procedure is automatically deleted when SQL Server is shut down because tempdb is re-created every time SQL Server is started. Procedures created directly in tempdb exist even after the creating connection is terminated. As with any other object, permissions to execute the temporary stored procedure can be granted, denied, and revoked to other users.
Creates a stored procedure, which is a saved collection of Transact-SQL statements that can take and return user-supplied parameters.
Procedures can be created for permanent use or for temporary use within a session (local temporary procedure) or for temporary use within all sessions (global temporary procedure).
Stored procedures can also be created to run automatically when Microsoft® SQL Server™ starts.
CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ]
procedure_name
Is the name of the new stored procedure. Procedure names must conform to the rules for identifiers and must be unique within the database and its owner. For more information, see Using Identifiers.
Local or global temporary procedures can be created by preceding the procedure_name with a single number sign (#procedure_name) for local temporary procedures and a double number sign (##procedure_name) for global temporary procedures. The complete name, including # or ##, cannot exceed 128 characters. Specifying the procedure owner name is optional.
;number
Is an optional integer used to group procedures of the same name so they can be dropped together with a single DROP PROCEDURE statement. For example, the procedures used with an application called orders may be named orderproc;1, orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the entire group. If the name contains delimited identifiers, the number should not be included as part of the identifier; use the appropriate delimiter around procedure_name only.
@parameter
Is a parameter in the procedure. One or more parameters can be declared in a CREATE PROCEDURE statement. The value of each declared parameter must be supplied by the user when the procedure is executed (unless a default for the parameter is defined). A stored procedure can have a maximum of 2,100 parameters.
Specify a parameter name using an at sign (@) as the first character. The parameter name must conform to the rules for identifiers. Parameters are local to the procedure; the same parameter names can be used in other procedures. By default, parameters can take the place only of constants; they cannot be used in place of table names, column names, or the names of other database objects. For more information, see EXECUTE.
data_type
Is the parameter data type. All data types, including text, ntext and image, can be used as a parameter for a stored procedure. However, the cursor data type can be used only on OUTPUT parameters. When you specify a data type of cursor, the VARYING and OUTPUT keywords must also be specified. For more information about SQL Server - supplied data types and their syntax, see Data Types.
Note There is no limit on the maximum number of output parameters that can be of cursor data type.
VARYING
Specifies the result set supported as an output parameter (constructed dynamically by the stored procedure and whose contents can vary). Applies only to cursor parameters.
default
Is a default value for the parameter. If a default is defined, the procedure can be executed without specifying a value for that parameter. The default must be a constant or it can be NULL. It can include wildcard characters (%, _, [], and [^]) if the procedure uses the parameter with the LIKE keyword.
OUTPUT
Indicates that the parameter is a return parameter. The value of this option can be returned to EXEC[UTE]. Use OUTPUT parameters to return information to the calling procedure. Text, ntext, and image parameters can be used as OUTPUT parameters. An output parameter using the OUTPUT keyword can be a cursor placeholder.
n
Is a placeholder indicating that a maximum of 2,100 parameters can be specified.
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
RECOMPILE indicates that SQL Server does not cache a plan for this procedure and the procedure is recompiled at run time. Use the RECOMPILE option when using atypical or temporary values without overriding the execution plan cached in memory.
ENCRYPTION indicates that SQL Server encrypts the syscomments table entry containing the text of the CREATE PROCEDURE statement. Using ENCRYPTION prevents the procedure from being published as part of SQL Server replication.
Note During an upgrade, SQL Server uses the encrypted comments stored in syscomments to re-create encrypted procedures.
FOR REPLICATION
Specifies that stored procedures created for replication cannot be executed on the Subscriber. A stored procedure created with the FOR REPLICATION option is used as a stored procedure filter and only executed during replication. This option cannot be used with the WITH RECOMPILE option.
AS
Specifies the actions the procedure is to take.
sql_statement
Is any number and type of Transact-SQL statements to be included in the procedure. Some limitations apply.
Is a placeholder that indicates multiple Transact-SQL statements may be included in this procedure.
The maximum size of a stored procedure is 128 MB.
A user-defined stored procedure can be created only in the current database (except for temporary procedures, which are always created in tempdb). The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch.
Parameters are nullable by default. If a NULL parameter value is passed and that parameter is used in a CREATE or ALTER TABLE statement in which the column referenced does not allow NULLs, SQL Server generates an error. To prevent passing a NULL parameter value to a column that does not allow NULLs, add programming logic to the procedure or use a default value (with the DEFAULT keyword of CREATE or ALTER TABLE) for the column.
It is recommended that you explicitly specify NULL or NOT NULL for each column in any CREATE TABLE or ALTER TABLE statement in a stored procedure, such as when creating a temporary table. The ANSI_DFLT_ON and ANSI_DFLT_OFF options control the way SQL Server assigns the NULL or NOT NULL attributes to columns if not specified in a CREATE TABLE or ALTER TABLE statement. If a connection executes a stored procedure with different settings for these options than the connection that created the procedure, the columns of the table created for the second connection can have different nullability and exhibit different behaviors. If NULL or NOT NULL is explicitly stated for each column, the temporary tables are created with the same nullability for all connections that execute the stored procedure.
SQL Server saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a stored procedure is created or altered. These original settings are used when the stored procedure is executed. Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored during stored procedure execution. SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements that occur within the stored procedure do not affect the functionality of the stored procedure.
Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a stored procedure is created or altered. If the logic of the stored procedure is dependent on a particular setting, include a SET statement at the start of the procedure to ensure the proper setting. When a SET statement is executed from a stored procedure, the setting remains in effect only until the stored procedure completes. The setting is then restored to the value it had when the stored procedure was called. This allows individual clients to set the options wanted without affecting the logic of the stored procedure.
Note Whether SQL Server interprets an empty string as either a single space or as a true empty string is controlled by the compatibility level setting. If the compatibility level is less than or equal to 65, SQL Server interprets empty strings as single spaces. If the compatibility level is equal to 70, SQL Server interprets empty strings as empty strings. For more information, see sp_dbcmptlevel.
To display the text used to create the procedure, execute sp_helptext in the database in which the procedure exists with the procedure name as the parameter.
Note Stored procedures created with the ENCRYPTION option cannot be viewed with sp_helptext.
For a report on the objects referenced by a procedure, use sp_depends.
To rename a procedure, use sp_rename.
SQL Server allows the creation of stored procedures that reference objects that do not yet exist. At creation time, only syntax checking is done. The stored procedure is compiled to generate an execution plan when executed, if a valid plan does not already exist in the cache. Only during compilation are all objects referenced in the stored procedure resolved. Thus, a syntactically correct stored procedure that references objects which do not exist can be created successfully, but will fail at run time because referenced objects do not exist. For more information, see Deferred Name Resolution and Compilation.
SQL Server allows Transact-SQL stored procedures to refer to tables that do not exist at creation time. This ability is called deferred name resolution. If, however, the Transact-SQL stored procedure refers to a table defined within the stored procedure, a warning is issued at creation time if the compatibility level setting (set by executing sp_dbcmptlevel) is 65. An error message is returned at run time if the table referenced does not exist. For more information, see sp_dbcmptlevel and Deferred Name Resolution and Compilation.
When a CREATE PROCEDURE statement is executed successfully, the procedure name is stored in the sysobjects system table and the text of the CREATE PROCEDURE statement is stored in syscomments. When executed for the first time, the procedure is compiled to determine an optimal access plan to retrieve the data.
Stored procedures can use the cursor data type only for OUTPUT parameters. If the cursor data type is specified for a parameter, both the VARYING and OUTPUT parameters are required. If the VARYING keyword is specified for a parameter, the data type must be cursor and the OUTPUT keyword must be specified.
Note The cursor data type cannot be bound to application variables through the database APIs such as OLE DB, ODBC, ADO, and DB-Library. Because OUTPUT parameters must be bound before an application can execute a stored procedure, stored procedures with cursor OUTPUT parameters cannot be called from the database APIs. These procedures can be called from Transact-SQL batches, stored procedures, or triggers only when the cursor OUTPUT variable is assigned to a Transact-SQL local cursor variable.
The following rules pertain to cursor output parameters when the procedure is executed:
Note An empty result set is not the same as a null value.
Note The closed state matters only at return time. For example, it is valid to close a cursor part way through the procedure, to open it again later in the procedure, and return that cursor's result set to the calling batch, stored procedure, or trigger.
SQL Server supports two types of temporary procedures: local and global. A local temporary procedure is visible only to the connection that created it. A global temporary procedure is available to all connections. Local temporary procedures are automatically dropped at the end of the current session. Global temporary procedures are dropped at the end of the last session using the procedure. Usually, this is when the session that created the procedure ends.
Temporary procedures named with # and ## can be created by any user. When the procedure is created, the owner of the local procedure is the only one who can use it. Permission to execute a local temporary procedure cannot be granted for other users. If a global temporary procedure is created, all users can access it; permissions cannot be revoked explicitly. Explicitly creating a temporary procedure in tempdb (naming without a number sign) can be performed only by those with explicit CREATE PROCEDURE permission in the tempdb database. Permission can be granted and revoked from these procedures.
Note Heavy use of temporary stored procedures can create contention on the system tables in tempdb and adversely affect performance. It is recommended that sp_executesql be used instead. sp_executesql does not store data in the system tables and therefore avoids the problem.
One or more stored procedures can execute automatically when SQL Server starts. The stored procedures must be created by the system administrator and executed under the sysadmin fixed server role as a background process. The procedure(s) cannot have any input parameters.
There is no limit to the number of startup procedures you can have, but be aware that each consumes one connection while executing. If you must execute multiple procedures at startup but do not need to execute them in parallel, make one procedure the startup procedure and have that procedure call the other procedures. This uses only one connection.
Execution of the stored procedures starts when the last database is recovered at startup. To skip launching these stored procedures, specify trace flag 4022 as a startup parameter. If you start SQL Server with minimal configuration (using the -f flag), the startup stored procedures are not executed. For more information, see Trace Flags.
To create a startup stored procedure, you must be logged in as a member of the sysadmin fixed server role and create the stored procedure in the master database.
Use sp_procoption to:
Stored procedures can be nested; that is one stored procedure calling another. The nesting level is incremented when the called procedure starts execution, and decremented when the called procedure finishes execution. Exceeding the maximum levels of nesting causes the whole calling procedure chain to fail. The current nesting level is returned by the @@NESTLEVEL function.
To estimate the size of a compiled stored procedure, use these Performance Monitor Counters.
* These counters are available for various categories of cache objects including adhoc sql, prepared sql, procedures, triggers, and so on.
For more information, see SQL Server: Buffer Manager Object and SQL Server: Cache Manager Object.
Any SET statement can be specified inside a stored procedure except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL, which must be the only statements in the batch. The SET option chosen remains in effect during the execution of the stored procedure and then reverts to its former setting.
Inside a stored procedure, object names used with certain statements must be qualified with the name of the object owner if other users are to use the stored procedure. The statements are:
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.
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 EXECUTE
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
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 EXECUTE 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 ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname = @firstnameAND 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' -- Or EXEC au_info 'Dull', 'Ann' -- Or EXEC au_info @lastname = 'Dull', @firstname = 'Ann' -- Or EXEC au_info @firstname = 'Ann', @lastname = 'Dull'
au_info 'Dull', 'Ann' -- Or au_info @lastname = 'Dull', @firstname = 'Ann' -- Or au_info @firstname = 'Ann', @lastname = 'Dull'
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 EXECUTE
The au_info2 stored procedure can be executed in many combinations. Only a few combinations are shown here:
EXECUTE au_info2 -- Or EXECUTE au_info2 'Wh%' -- Or EXECUTE au_info2 @firstname = 'A%' -- Or EXECUTE au_info2 '[CK]ars[OE]n' -- Or EXECUTE au_info2 'Hunter', 'Sheryl' -- Or EXECUTE au_info2 'H%', 'S%'
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 EXECUTE CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUT AS SELECT 'Title Name' = title FROM titles WHERE title LIKE @@TITLE SELECT @@SUM = SUM(price) FROM titles WHERE title LIKE @@TITLE GO
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.
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 DEALLOCATE @MyCursor GO
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 EXECUTE
The WITH ENCRYPTION clause hides the text of a stored procedure from users. This example creates an encrypted procedure, uses the sp_helptext system stored procedure to get information on that encrypted 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
The object's comments have been encrypted.
Next, select the identification number and text of the encrypted 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'
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)
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 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 EXECUTE USE master EXECUTE 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 EXECUTE USE pubs EXEC sp_showindexes 'emp%' EXECUTE
TABLE_NAME INDEX_NAME INDEX_ID --------------- ---------------- employee employee_ind 1 employee PK_emp_id 2 (2 row(s) affected)
This example shows four procedures and the various ways that deferred name resolution can be used. Each stored procedure is created, although the table or column referenced does not exist at compile time.
Alters a previously created procedure, created by executing the CREATE PROCEDURE statement,
without changing permissions and without
affecting any dependent stored procedures or triggers. For more information about the parameters
used in the ALTER PROCEDURE statement,
see CREATE PROCEDURE.
ALTER PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ]
Is the name of the procedure to change. Procedure names must conform to the rules for identifiers.
Is an existing optional integer used to group procedures of the same name so that they can be
dropped together with a single DROP PROCEDURE statement.
Is a parameter in the procedure.
Is the data type of the parameter.
Specifies the result set supported as an output parameter (constructed dynamically by the stored
procedure and whose contents can vary). Applies only to cursor parameters.
Is a default value for the parameter.
Indicates that the parameter is a return parameter.
Is a placeholder indicating up to 2,100 parameters can be specified.
RECOMPILE indicates that Microsoft® SQL Server™ does not cache a plan
for this procedure and the procedure is recompiled at run time.
ENCRYPTION indicates that SQL Server encrypts the syscomments table
entry that contains the text of the
ALTER PROCEDURE statement. Using ENCRYPTION prevents the procedure
from being published as part of SQL Server replication.
Note During an upgrade, SQL Server uses the encrypted comments
stored in syscomments to re-create encrypted procedures.
Specifies that stored procedures created for replication cannot be executed
on the Subscriber. A stored procedure created
with the FOR REPLICATION option is used as a stored procedure filter
and only executed during replication. This option cannot
be used with the WITH RECOMPILE option.
Are the actions the procedure is to take.
Is any number and type of Transact-SQL statements to be included in the procedure.
Some limitations do apply. For more information, see sql_statement Limitations in CREATE PROCEDURE.
Is a placeholder indicating that multiple Transact-SQL statements can be included
in the procedure. For more information, see CREATE PROCEDURE.
For more information about ALTER PROCEDURE, see Remarks in CREATE PROCEDURE.
Note If a previous procedure definition was created using WITH ENCRYPTION or
WITH RECOMPILE, these options are only enabled if they are included in ALTER PROCEDURE.
ALTER PROCEDURE permissions default to members of the sysadmin fixed server role,
and the db_owner and db_ddladmin fixed database roles, and the owner of the procedure,
and are not transferable.
Permissions and the startup property remain unchanged for a procedure modified with ALTER PROCEDURE.
This example creates a procedure called Oakland_authors that, by default, contains all
authors from the city of Oakland, California. Permissions are granted. Then, when the
procedure must be changed to retrieve all authors from California, ALTER PROCEDURE is
used to redefine the stored procedure.
USE pubs GO IF EXISTS(SELECT name FROM sysobjects WHERE name = 'Oakland_authors' AND type = 'P') DROP PROCEDURE Oakland_authors GO -- Create a procedure from the authors table that contains author -- information for those authors who live in Oakland, California. USE pubs GO CREATE PROCEDURE Oakland_authors AS SELECT au_fname, au_lname, address, city, zip FROM pubs..authors WHERE city = 'Oakland' and state = 'CA' ORDER BY au_lname, au_fname GO -- Here is the statement to actually see the text of the procedure. SELECT o.id, c.text FROM sysobjects o INNER JOIN syscomments c ON o.id = c.id WHERE o.type = 'P' and o.name = 'Oakland_authors' -- Here, EXECUTE permissions are granted on the procedure to public. GRANT EXECUTE ON Oakland_authors TO public GO -- The procedure must be changed to include all -- authors from California, regardless of what city they live in. -- If ALTER PROCEDURE is not used but the procedure is dropped -- and then re-created, the above GRANT statement and any -- other statements dealing with permissions that pertain to this -- procedure must be re-entered. ALTER PROCEDURE Oakland_authors WITH ENCRYPTION AS SELECT au_fname, au_lname, address, city, zip FROM pubs..authors WHERE state = 'CA' ORDER BY au_lname, au_fname GO Here is the statement to actually see the text of the procedure. SELECT o.id, c.text FROM sysobjects o INNER JOIN syscomments c ON o.id = c.id WHERE o.type = 'P' and o.name = 'Oakland_authors' GO
Removes one or more stored procedures or procedure groups from the current database.
DROP PROCEDURE { procedure } [ ,...n ]
procedure
Is name of the stored procedure or stored procedure group to be removed.
Procedure names must conform to the rules for identifiers. For more information, see Using Identifiers.
Specifying the procedure owner name is optional, and a server name or database name cannot be specified.
Is a placeholder indicating that multiple procedures can be specified.
To see a list of procedure names, use sp_help. To display the procedure definition (which is stored in the
syscomments system table), use sp_helptext. When a stored procedure is dropped, information about the procedure
is removed from the sysobjects and syscomments system tables.
Individual procedures in the group cannot be dropped; the entire procedure group is dropped.
User-defined system procedures (prefixed with sp_) are dropped from the master database whether or not it is
the current database. If the system procedure is not found in the current database, Microsoft® SQL Server™
tries to drop it from the master database.
DROP PROCEDURE permissions default to the procedure owner and are not transferable. However,
members of the db_owner and db_ddladmin fixed database roles and the sysadmin fixed
server role can drop any object by specifying the owner in DROP PROCEDURE.
This example removes the byroyalty stored procedure (in the current database).
The StoredProcedure object exposes the attributes of a single Microsoft® SQL Server™
user-defined or system stored procedure.
SQL Server has facilities for creation and persistent storage of compiled Transact-SQL scripts. These stored
procedures can be executed by users with sufficient permissions. With the StoredProcedure object, you can:
The Name property of a StoredProcedure object uses the SQL Server data type sysname.
The value of the Name property must be unique (named by owner) within a SQL Server database.
To create a SQL Server stored procedure
Note The StoredProcedure object is compatible with instances of SQL Server 2000 and SQL Server version 7.0.
However, the StoredProcedure2 object extends the functionality of the StoredProcedure object for use with
features that are new in SQL Server 2000.
If you specify the OUTPUT keyword for a parameter in the procedure definition, the stored procedure can return the current value of the parameter to the calling program when the stored procedure exits. To save the value of the parameter in a variable that can be used in the calling program, the calling program must use the OUTPUT keyword when executing the stored procedure.
The following example shows a stored procedure with an input and an output parameter. The first parameter in the stored procedure @title receives the input value specified by the calling program, and the second parameter @ytd_sales is used to return the value to the calling program. The SELECT statement uses the @title parameter to obtain the correct ytd_sales value, and assigns the value to the @ytd_sales output parameter.
CREATE PROCEDURE get_sales_for_title @title varchar(80), -- This is the input parameter. @ytd_sales int OUTPUT -- This is the output parameter. AS -- Get the sales for the specified title and -- assign it to the output parameter. SELECT @ytd_sales = ytd_sales FROM titles WHERE title = @title RETURN GO
The following program executes the stored procedure with a value for the input parameter and saves the output value of the stored procedure in the @ytd_sales_for_title variable local to the calling program.
-- Declare the variable to receive the output value of the procedure. DECLARE @ytd_sales_for_title int -- Execute the procedure with a title_id value -- and save the output value in a variable. EXECUTE get_sales_for_title "Sushi, Anyone?", @ytd_sales = @ytd_sales_for_title OUTPUT -- Display the value returned by the procedure. PRINT 'Sales for "Sushi, Anyone?": ' + convert(varchar(6),@ytd_sales_for_title) GO Sales for "Sushi, Anyone?": 4095
Input values can also be specified for OUTPUT parameters when the stored procedure is executed. This allows the stored procedure to receive a value from the calling program, change it or perform operations with it, then return the new value to the calling program. In the earlier example, the @ytd_sales_for_title variable can be assigned a value prior to executing the stored procedure. The @ytd_sales variable contains the value of the parameter in the body of the stored procedure, and the value of the @ytd_sales variable is returned to the calling program when the stored procedure exits. This is often referred to as "pass-by-reference capability."
If you specify OUTPUT for a parameter when you execute a stored procedure and the parameter is not defined using OUTPUT in the stored procedure, you get an error message. You can execute a stored procedure with OUTPUT parameters and not specify OUTPUT when executing the stored procedure. No error is returned, but you cannot use the output value in the calling program.
A stored procedure can return an integer value called a return code to indicate the execution status of a procedure. You specify the return code for a stored procedure using the RETURN statement. As with OUTPUT parameters, you must save the return code in a variable when the stored procedure is executed to use the return code value in the calling program. For example, the assignment variable @result of data type int is used to store the return code from the stored procedure my_proc:
DECLARE @result int EXECUTE @result = my_proc
Return codes are commonly used in control-of-flow blocks within stored procedures to set the return code value for each possible error situation. You can use the @@ERROR function after a Transact-SQL statement to detect if an error occurred during the execution of the statement.
This example shows the get_sales_for_title procedure with special handling that sets special return code values for various errors. The table shows the integer value assigned by the stored procedure to each possible error.
CREATE PROCEDURE get_sales_for_title -- This is the input parameter, with a default. @title varchar(80) = NULL, -- This is the output parameter. @ytd_sales int OUTPUT AS -- Validate the @title parameter. IF @title IS NULL BEGIN PRINT "ERROR: You must specify a title value." RETURN(1) END ELSE BEGIN -- Make sure the title is valid. IF (SELECT COUNT(*) FROM titles WHERE title = @title) = 0 RETURN(2) END -- Get the sales for the specified title and -- assign it to the output parameter. SELECT @ytd_sales = ytd_sales FROM titles WHERE title = @title -- Check for SQL Server errors. IIF @@ERROR <> 0 BEGIN RETURN(3) END ELSE BEGIN -- Check to see if the ytd_sales value is NULL. IF @ytd_sales IS NULL RETURN(4) ELSE -- SUCCESS!! RETURN(0) END GO
Using return codes in this manner allows your calling programs to detect and handle the errors that occur when the stored procedure is executed.
This example creates a program to handle the return codes returned from the get_sales_for_title procedure.
-- Declare the variables to receive the output value and return code -- of the procedure. DECLARE @ytd_sales_for_title int, @ret_code INT -- Execute the procedure with a title_id value -- and save the output value and return code in variables. EXECUTE @ret_code = get_sales_for_title "Sushi, Anyone?", @ytd_sales = @ytd_sales_for_title OUTPUT -- Check the return codes. IF @ret_code = 0 BEGIN PRINT "Procedure executed successfully" -- Display the value returned by the procedure. PRINT 'Sales for "Sushi, Anyone?": ' + CONVERT(varchar(6),@ytd_sales_for_title) END ELSE IF @ret_code = 1 PRINT"ERROR: No title_id was specified." ELSE IF @ret_code = 2 PRINT "ERROR: An invalid title_id was specified." ELSE IF @ret_code = 3 PRINT "ERROR: An error occurred getting the ytd_sales." GO
If @@TRANCOUNT has a different value when a stored procedure finishes than it had when the procedure was executed, an informational error 266 is generated. This error is not generated by the same condition in triggers.
A 266 error is generated when a stored procedure is called with an @@TRANCOUNT of 1 or greater and the procedure executes a ROLLBACK TRANSACTION or ROLLBACK WORK statement. This is because ROLLBACK rolls back all outstanding transactions and decrements @@TRANCOUNT to 0, which is a lower value than it had when the procedure was called.
If a ROLLBACK TRANSACTION is issued in a trigger:
A trigger always operates as if there were an outstanding transaction in effect when the trigger is executed. This is definitely true if the statement firing the trigger is in an implicit or explicit transaction. It is also true in autocommit mode. When a statement begins executing in autocommit mode, there is an implied BEGIN TRANSACTION to allow the recovery of all modifications generated by the statement if it encounters an error. This implied transaction has no effect on the other statements in the batch because it is either committed or rolled back when the statement completes. This implied transaction is still in effect, however, when a trigger is called.
This means that any time a BEGIN TRANSACTION statement is issued in the trigger, it is actually beginning a nested transaction. Because a nested BEGIN TRANSACTION statement is ignored when rolling back nested transactions, ROLLBACK TRANSACTION issued in the trigger always rolls back past any BEGIN TRANSACTION statements issued by the trigger itself. ROLLBACK rolls back to the outermost BEGIN TRANSACTION.
You must use the SAVE TRANSACTION statement to do a partial rollback in a trigger, even if it is always called in autocommit mode. This is illustrated by the following trigger:
CREATE TRIGGER TestTrig ON TestTab FOR UPDATE AS SAVE TRANSACTION MyName INSERT INTO TestAudit SELECT * FROM inserted IF (@@error <> 0) BEGIN ROLLBACK TRANSACTION MyName END
This also affects COMMIT TRANSACTION statements that follow a BEGIN TRANSACTION statement in a trigger. Because BEGIN TRANSACTION starts a nested transaction, a subsequent COMMIT statement applies only to the nested transaction. If a ROLLBACK TRANSACTION statement is executed after COMMIT, ROLLBACK rolls back everything to the outermost BEGIN TRANSACTION. This is illustrated by the following trigger:
CREATE TRIGGER TestTrig ON TestTab FOR UPDATE AS BEGIN TRANSACTION INSERT INTO TrigTarget SELECT * FROM inserted COMMIT TRANSACTION ROLLBACK TRANSACTION
This trigger will never insert into the TrigTarget table. BEGIN TRANSACTION always starts a nested transaction. COMMIT TRANSACTION commits only the nested transaction, while the following ROLLBACK TRANSACTION rolls everything back to the outermost BEGIN TRANSACTION.
All well-designed Microsoft® SQL Server™ 2000 applications should use stored procedures. This is true whether or not the business logic of the application is written into stored procedures. Even standard Transact-SQL statements with no business logic component gain a performance advantage when packaged as stored procedures with parameters. Transact-SQL statements compiled into stored procedures can save a significant amount of processing at execution time.Another advantage of stored procedures is that client execution requests use the network more efficiently than equivalent Transact-SQL statements sent to the server. For example, suppose an application needs to insert a large binary value into an image data column. To send the data in an INSERT statement, the application must convert the binary value to a character string (doubling its size), and then send it to the server. The server then converts the value back into a binary format for storage in the image column. In contrast, the application can create a stored procedure of the form:
CREATE PROCEDURE P(@p1 image) AS INSERT T VALUES (@p1)
When the client application requests an execution of procedure P, the image parameter value will stay in binary format all the way to the server, thereby saving processing time and network traffic.
SQL Server stored procedures can provide even greater performance gains when they include business services logic because it moves the processing to the data, rather than moving the data to the processing.
