CREATE PROCEDURE (Transact-SQL)
Creates a stored procedure. A stored procedure is a saved collection of Transact-SQL statements or a reference to a Microsoft .NET Framework common language runtime (CLR) method 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 an instance of SQL Server starts.
.gif "Topic link icon")
Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ;number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ =default ] [ OUT | OUTPUT ] [READONLY]
] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { <sql_statement> [;][ ...n ] | <method_specifier> }
[;]
<procedure_option> ::=
[ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE AS Clause ]
<sql_statement> ::=
{ [ BEGIN ] statements [ END ] }
<method_specifier> ::=
EXTERNAL NAME assembly_name.class_name.method_name
Arguments
schema_name
Is the name of the schema to which the procedure belongs.procedure_name
Is the name of the new stored procedure. Procedure names must comply with the rules for identifiers and must be unique within the schema.We strongly recommend that you not use the prefix sp_ in the procedure name. This prefix is used by SQL Server to designate system stored procedures. For more information, see Creating Stored Procedures (Database Engine).
Local or global temporary procedures can be created by using one number sign (#) before procedure_name (#procedure_name) for local temporary procedures, and two number signs for global temporary procedures (##procedure_name). Temporary names cannot be specified for CLR stored procedures.
The complete name for a stored procedure or a global temporary stored procedure, including ##, cannot exceed 128 characters. The complete name for a local temporary stored procedure, including #, cannot exceed 116 characters.
**;**number
Is an optional integer that is used to group procedures of the same name. These grouped procedures can be dropped together by using one DROP PROCEDURE statement. For example, an application called orders might use procedures named orderproc;1, orderproc;2, and so on. The DROP PROCEDURE orderproc statement drops the whole group. If the name contains delimited identifiers, the number should not be included as part of the identifier; use the appropriate delimiter around only procedure_name.Numbered stored procedures have the following restrictions:
Cannot use xml or CLR user-defined types as the data types.
Cannot create a plan guide on a numbered stored procedure.
Note
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
**@**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 called, unless a default for the parameter is defined or the value is set to equal another parameter. A stored procedure can have a maximum of 2,100 parameters. If a procedure contains table-valued parameters, and the parameter is missing in the call, a default of empty table is passed in.Specify a parameter name by using an at sign (@) as the first character. The parameter name must comply with 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 constant expressions; they cannot be used instead of table names, column names, or the names of other database objects. For more information, see EXECUTE (Transact-SQL).
Parameters cannot be declared if FOR REPLICATION is specified.
[ type_schema_name**.** ] data_type
Is the data type of the parameter and the schema to which it belongs. All data types, can be used as a parameter for a Transact-SQL stored procedure. You can use a user-defined table type to declare a table-valued parameter as to a parameter for a Transact-SQL stored procedure. Table-valued parameters can be specified as input parameters only, and they must be accompanied by a READONLY keyword. The cursor data type can be used only on OUTPUT parameters. When you specify a cursor data type, the VARYING and OUTPUT keywords must also be specified. You can have multiple output parameters specified with the cursor data type.For CLR stored procedures, char, varchar, text, ntext, image, cursor, user-define table types and table cannot be specified as parameters. For more information about the correspondence between CLR types and SQL Server system data types, see Mapping CLR Parameter Data. For more information about SQL Server system data types and their syntax, see Data Types (Transact-SQL).
If the data type of the parameter is a CLR user-defined type, you must have EXECUTE permission on the type.
If type_schema_name is not specified, the SQL Server Database Engine references type_name in the following order:
The SQL Server system data types.
The default schema of the current user in the current database.
The dbo schema in the current database.
For numbered stored procedures, the data type cannot be xml or a CLR user-defined type.
VARYING
Specifies the result set supported as an output parameter. This parameter is dynamically constructed by the stored procedure and its contents may vary. Applies only to cursor parameters.default
Is a default value for the parameter. If a default value 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. If the procedure uses the parameter with the LIKE keyword, it can include the following wildcard characters: % _ [] and [^].Note
Default values are recorded in the sys.parameters.default column only for CLR procedures. That column will be NULL for Transact-SQL procedure parameters.
OUTPUT
Indicates that the parameter is an output parameter. The value of this option can be returned to the calling EXECUTE statement. Use OUTPUT parameters to return values to the caller of the procedure. text, ntext, and image parameters cannot be used as OUTPUT parameters, unless the procedure is a CLR procedure. An output parameter that uses the OUTPUT keyword can be a cursor placeholder, unless the procedure is a CLR procedure. A user-defined table type cannot be specified as an OUTPUT parameter of a stored procedure.READONLY
Indicate that the parameter cannot be updated or modified within the body of the procedure. If the parameter type is a user-defined table type, READONLY must be specified.RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure and the procedure is compiled at run time. This option cannot be used when FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored procedures.To instruct the Database Engine to discard plans for individual queries inside a stored procedure, use the RECOMPILE query hint. For more information, see Query Hints (Transact-SQL). Use the RECOMPILE query hint when atypical or temporary values are used in only a subset of queries that belong to the stored procedure.
ENCRYPTION
Indicates that SQL Server will convert the original text of the CREATE PROCEDURE statement to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server. Users that have no access to system tables or database files cannot retrieve the obfuscated text. However, the text will be available to privileged users that can either access system tables over the DAC port or directly access database files. Also, users that can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime. For more information about accessing system metadata, see Metadata Visibility Configuration.This option is not valid for CLR stored procedures.
Procedures created by using this option cannot be published as part of SQL Server replication.
EXECUTE AS
Specifies the security context under which to execute the stored procedure.For more information, see EXECUTE AS Clause (Transact-SQL).
FOR REPLICATION
Specifies that stored procedures that are 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 is executed only during replication. Parameters cannot be declared if FOR REPLICATION is specified. FOR REPLICATION cannot be specified for CLR stored procedures. The RECOMPILE option is ignored for procedures created with FOR REPLICATION.A FOR REPLICATION procedure will have an object type RF in sys.objects and sys.procedures.
<sql_statement>
Is one or more Transact-SQL statements to be included in the procedure. For information about some limitations that apply, see the Remarks section.EXTERNAL NAME assembly_name**.class_name.method_name
Specifies the method of a .NET Framework assembly for a CLR stored procedure to reference. class_name must be a valid SQL Server identifier and must exist as a class in the assembly. If the class has a namespace-qualified name that uses a period (.) to separate namespace parts, the class name must be delimited by using brackets ([** ]) or quotation marks (" "). The specified method must be a static method of the class.Note
By default, SQL Server cannot execute CLR code. You can create, modify, and drop database objects that reference common language runtime modules; however, you cannot execute these references in SQL Server until you enable the clr enabled option. To enable the option, use sp_configure.
Remarks
There is no predefined maximum size of a stored procedure.
A user-defined stored procedure can be created only in the current database. Temporary procedures are an exception to this because they are always created in tempdb. If a schema name is not specified, the default schema of the user that is creating the procedure is used. For more information about schemas, see User-Schema Separation.
The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch.
By default, parameters are nullable. If a NULL parameter value is passed and that parameter is used in a CREATE TABLE or ALTER TABLE statement in which the referenced column does not allow null values, the Database Engine generates an error. To prevent passing NULL to a column that does not allow for null values, add programming logic to the procedure or use a default value for the column by using the DEFAULT keyword of CREATE TABLE or ALTER TABLE.
We recommend that you explicitly specify NULL or NOT NULL for each column in a temporary table. The ANSI_DFLT_ON and ANSI_DFLT_OFF options control the way the Database Engine assigns the NULL or NOT NULL attributes to columns when these attributes are 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 behavior. If NULL or NOT NULL is explicitly stated for each column, the temporary tables are created by using the same nullability for all connections that execute the stored procedure.
Using SET Options
The Database Engine saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a Transact-SQL stored procedure is created or modified. 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 when the stored procedure is running. Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a stored procedure is created or modified. If the logic of the stored procedure depends on a particular setting, include a SET statement at the start of the procedure to guarantee the appropriate setting. When a SET statement is executed from a stored procedure, the setting remains in effect only until the stored procedure has finished running. The setting is then restored to the value the stored procedure had when it was called. This enables individual clients to set the options they want without affecting the logic of the stored procedure.
Note
ANSI_WARNINGS is not honored when you pass parameters in a stored procedure, user-defined function, or when you declare and set variables in a batch statement. For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.
Using Parameters with CLR Stored Procedures
The parameters of a CLR stored procedure can be any one of the scalar SQL Server system data types.
For the Database Engine to reference the correct method when it is overloaded in the .NET Framework, the method indicated in <method_specifier> must have the following characteristics:
Be declared as a static method.
Receive the same number of parameters as the number of parameters of the procedure.
Not be a constructor or destructor of its class.
Use parameter types that are compatible with the data types of the corresponding parameters of the SQL Server procedure. For information about matching SQL Server data types to the .NET Framework data types, see Mapping CLR Parameter Data.
Return either void or a value of type SQLInt32, SQLInt16, System.Int32, or System.Int16.
Return its parameters by reference, not by value, when OUTPUT is specified for any specific parameter declaration.
Getting Information About Stored Procedures
To display the definition of a Transact-SQL stored procedure, use the sys.sql_modules catalog view in the database in which the procedure exists.
For example:
USE AdventureWorks;
GO
SELECT definition
FROM sys.sql_modules
JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND TYPE = 'P';
Note
The text of a stored procedure created with the ENCRYPTION option cannot be viewed by using the sys.sql_modules catalog view.
For a report about the objects referenced by a procedure, query the sys.sql_expression_dependencies catalog view or use sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities.
To display information about CLR stored procedures, use the sys.assembly_modules catalog view in the database in which the procedure exists.
To display information about the parameters that are defined in a stored procedure, use the sys.parameters catalog view in the database in which the procedure exists.
Deferred Name Resolution
You can create stored procedures that reference tables that do not yet exist. At creation time, only syntax checking is performed. The stored procedure is not compiled until it is executed for the first time. Only during compilation are all objects referenced in the stored procedure resolved. Therefore, a syntactically correct stored procedure that references tables that do not exist can be created successfully; however, the stored procedure will fail at run time if the referenced tables do not exist. For more information, see Deferred Name Resolution and Compilation.
Executing Stored Procedures
When you execute a user-defined stored procedure, either in a batch or inside a module such as a user-defined stored procedure or function, we strongly recommend qualifying the stored procedure name with a schema name.
Parameter values can be supplied if a stored procedure is written to accept them. The supplied value must be a constant or a variable. You cannot specify a function name as a parameter value. Variables can be user-defined or system variables, such as @@SPID.
For more information, see Executing Stored Procedures (Database Engine).
When a procedure is executed for the first time, it is compiled to determine an optimal access plan to retrieve the data. Subsequent executions of the stored procedure may reuse the plan already generated if it still remains in the plan cache of the Database Engine. For more information, see Execution Plan Caching and Reuse.
Parameters That Use the cursor Data Type
Transact-SQL 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. For more information, see Using the cursor Data Type in an OUTPUT Parameter.
Temporary Stored Procedures
The Database Engine 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. For more information, see Creating Stored Procedures (Database Engine).
Automatically Executing Stored Procedures
One or more stored procedures can execute automatically when SQL Server starts. The stored procedures must be created by the system administrator in the master database and executed under the sysadmin fixed server role as a background process. The procedures cannot have any input or output parameters. For more information, see Automatic Execution of Stored Procedures.
Stored Procedure Nesting
Stored procedures can be nested. This means one stored procedure can call another. The nesting level is incremented when the called procedure starts running, and decremented when the called procedure finishes running. Stored procedures can be nested up to 32 levels. For more information, see Nesting Stored Procedures.
To estimate the size of a compiled stored procedure, use the following Performance Monitor Counters.
Performance Monitor object name |
Performance Monitor Counter name |
|---|---|
SQLServer: Plan Cache Object |
Cache Hit Ratio |
|
Cache Pages |
|
Cache Object Counts* |
* These counters are available for various categories of cache objects including ad hoc sql, prepared sql, procedures, triggers, and so on.
For more information, see SQL Server, Plan Cache Object.
<sql_statement> Limitations
Any SET statement can be specified inside a stored procedure, except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL. These 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 in all Data Definition Language (DDL) statements such as CREATE, ALTER, or DROP statements, DBCC statements, EXECUTE and dynamic SQL statements must be qualified with the name of the object schema if users other than the stored procedure owner are to use the stored procedure. For more information, see Designing Stored Procedures (Database Engine).
Permissions
Requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created.
For CLR stored procedures, requires ownership of the assembly referenced in <method_specifier>, or REFERENCES permission on that assembly.
Examples
A. Using a simple procedure
The following stored procedure returns all employees (first and last names supplied), their job titles, and their department names from a view. This stored procedure does not use any parameters.
USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL
DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
SET NOCOUNT ON;
SELECT LastName, FirstName, Department
FROM HumanResources.vEmployeeDepartmentHistory;
GO
The uspGetEmployees stored procedure can be executed in the following ways:
EXECUTE HumanResources.uspGetAllEmployees;
GO
-- Or
EXEC HumanResources.uspGetAllEmployees;
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetAllEmployees;
B. Using a simple procedure with parameters
The following stored procedure returns only the specified employee (first and last name supplied), her title, and her department name from a view. This stored procedure accepts exact matches for the parameters passed.
USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL
DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees
@LastName nvarchar(50),
@FirstName nvarchar(50)
AS
SET NOCOUNT ON;
SELECT FirstName, LastName, Department
FROM HumanResources.vEmployeeDepartmentHistory
WHERE FirstName = @FirstName AND LastName = @LastName;
GO
The uspGetEmployees stored procedure can be executed in the following ways:
EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
-- Or
EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar';
GO
-- Or
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
C. Using a simple procedure with wildcard parameters
The following stored procedure returns only the specified employees (first and last names supplied), their titles, and their departments from a view. This stored procedure pattern matches the parameters passed or, if not supplied, uses the preset default (last names that start with the letter D).
USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees2', 'P' ) IS NOT NULL
DROP PROCEDURE HumanResources.uspGetEmployees2;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees2
@LastName nvarchar(50) = N'D%',
@FirstName nvarchar(50) = N'%'
AS
SET NOCOUNT ON;
SELECT FirstName, LastName, Department
FROM HumanResources.vEmployeeDepartmentHistory
WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
GO
The uspGetEmployees2 stored procedure can be executed in many combinations. Only several combinations are shown here:
EXECUTE HumanResources.uspGetEmployees2;
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Wi%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 @FirstName = N'%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'[CK]ars[OE]n';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Hesse', N'Stefen';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'H%', N'S%';
D. Returning more than one result set
The following stored procedure returns two result sets.
USE AdventureWorks;
GO
CREATE PROCEDURE uspNResults
AS
SELECT COUNT(ContactID) FROM Person.Contact
SELECT COUNT(CustomerID) FROM Sales.Customer;
GO
E. Using OUTPUT parameters
The following example creates the uspGetList stored procedure. This procedures returns a list of products that have prices that do not exceed a specified amount. The example shows using multiple SELECT statements and multiple OUTPUT parameters. OUTPUT parameters enable an external procedure, a batch, or more than one Transact-SQL statement to access a value set during the procedure execution.
USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL
DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product varchar(40)
, @MaxPrice money
, @ComparePrice money OUTPUT
, @ListPrice money OUT
AS
SET NOCOUNT ON;
SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO
Execute uspGetList to return a list of Adventure Works products (Bikes) that cost less than $700. The OUTPUT parameters @Cost and @ComparePrices are used with control-of-flow language to return a message in the Messages window.
Note
The OUTPUT variable must be defined when the procedure is created and also when the variable is used. The parameter name and variable name do not have to match; however, the data type and parameter positioning must match, unless @ListPrice= variable is used.
DECLARE @ComparePrice money, @Cost money
EXECUTE Production.uspGetList '%Bikes%', 700,
@ComparePrice OUT,
@Cost OUTPUT
IF @Cost <= @ComparePrice
BEGIN
PRINT 'These products can be purchased for less than
$'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
PRINT 'The prices for all products in this category exceed
$'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
Here is the partial result set:
Product List Price
-------------------------------------------------- ------------------
Road-750 Black, 58 539.99
Mountain-500 Silver, 40 564.99
Mountain-500 Silver, 42 564.99
...
Road-750 Black, 48 539.99
Road-750 Black, 52 539.99
(14 row(s) affected)
These items can be purchased for less than $700.00.
F. Using the WITH RECOMPILE option
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 AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.uspProductByVendor;
GO
CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'
WITH RECOMPILE
AS
SET NOCOUNT ON;
SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'
FROM Purchasing.Vendor AS v
JOIN Purchasing.ProductVendor AS pv
ON v.VendorID = pv.VendorID
JOIN Production.Product AS p
ON pv.ProductID = p.ProductID
WHERE v.Name LIKE @Name;
GO
G. Using the WITH ENCRYPTION option
The following example creates the HumanResources.uspEncryptThis stored procedure.
USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspEncryptThis', 'P' ) IS NOT NULL
DROP PROCEDURE HumanResources.uspEncryptThis;
GO
CREATE PROCEDURE HumanResources.uspEncryptThis
WITH ENCRYPTION
AS
SET NOCOUNT ON;
SELECT EmployeeID, Title, NationalIDNumber, VacationHours, SickLeaveHours
FROM HumanResources.Employee;
GO
The WITH ENCRYPTION option prevents the definition of the stored procedure from being returned, as shown by the following examples.
Run sp_helptext:
EXEC sp_helptext 'HumanResources.uspEncryptThis';
Here is the result set.
The text for object 'HumanResources.uspEncryptThis' is encrypted.
Directly query the sys.sql_modules catalog view:
USE AdventureWorks;
GO
SELECT definition FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');
Here is the result set.
definition
----------------------
NULL
(1 row(s) affected)
H. Using deferred name resolution
The following example creates the uspProc1 procedure. It uses deferred name resolution. The stored procedure is created although the table that is referenced does not exist at compile time. However, the table must exist when the procedure is executed.
USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspProc1', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.uspProc1;
GO
CREATE PROCEDURE dbo.uspProc1
AS
SET NOCOUNT ON;
SELECT column1, column2 FROM table_does_not_exist
GO
To verify that the stored procedure has been created, run the following query:
USE AdventureWorks;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.uspproc1');
Here is the result set.
definition
-----------------------------------------------------------------------
CREATE PROCEDURE uspproc1
AS
SELECT column1, column2 FROM table_does_not_exist
(1 row(s) affected)
I. Using the EXECUTE AS clause
The following example shows using the EXECUTE AS clause to specify the security context in which a stored procedure can be executed. In the example, the option CALLER specifies that the procedure can be executed in the context of the user that calls it.
USE AdventureWorks;
GO
IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL
DROP PROCEDURE Purchasing.uspVendorAllInfo;
GO
CREATE PROCEDURE Purchasing.uspVendorAllInfo
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON;
SELECT v.Name AS Vendor, p.Name AS 'Product name',
v.CreditRating AS 'Credit Rating',
v.ActiveFlag AS Availability
FROM Purchasing.Vendor v
INNER JOIN Purchasing.ProductVendor pv
ON v.VendorID = pv.VendorID
INNER JOIN Production.Product p
ON pv.ProductID = p.ProductID
ORDER BY v.Name ASC;
GO
J. Creating a CLR stored procedure
The following example creates the GetPhotoFromDB stored procedure that references the GetPhotoFromDB method of the LargeObjectBinary class in the HandlingLOBUsingCLR assembly. Before the stored procedure is created, the HandlingLOBUsingCLR assembly is registered in the local database.
CREATE ASSEMBLY HandlingLOBUsingCLR
FROM '\\MachineName\HandlingLOBUsingCLR\bin\Debug\HandlingLOBUsingCLR.dll';
GO
CREATE PROCEDURE dbo.GetPhotoFromDB
(
@ProductPhotoID int,
@CurrentDirectory nvarchar(1024),
@FileName nvarchar(1024)
)
AS EXTERNAL NAME HandlingLOBUsingCLR.LargeObjectBinary.GetPhotoFromDB;
GO
K. Using 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 Currency table:
USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.uspCurrencyCursor;
GO
CREATE PROCEDURE dbo.uspCurrencyCursor
@CurrencyCursor CURSOR VARYING OUTPUT
AS
SET NOCOUNT ON;
SET @CurrencyCursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT CurrencyCode, Name
FROM Sales.Currency;
OPEN @CurrencyCursor;
GO
Next, run 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 AdventureWorks;
GO
DECLARE @MyCursor CURSOR;
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO
See Also