CREATE Procedure
Creating a stored procedure, the stored procedure is a collection of Transact-SQL statements that can be accepted and returned to the parameters provided by the user.
You can create a process for permanent use, or temporarily use in a session (local temporary process), or temporarily use in all sessions (global temporary process).
You can also create a stored procedure that is automatically run when Microsoft® SQL ServerTM startup.
grammar
Create Proc [EDURE] Procedure_name [; Number] [{@Parameter Data_Type} [Varying] [= default] [OUTPUT]] [, ... n] [with {recompile | encryption | Recompile, Encryption}] [for replication] As SQL_Statement [... n]
parameter
Procedure_name
The name of the new store process. The process name must meet the identifier rules and must be unique to the database and owners. For more information, see Using the Identifier.
To create a local temporary process, you can add a number (#procedure_name) in front of Procedure_name, to create a global temporary process, can add two numbered characters (## procedure_name) in front of Procedure_name. The full name (including # or ##) cannot exceed 128 characters. The name of the specified process owner is optional.
Number
It is an optional integer that is used to group the process of the same name to remove the same group of procedures with a Drop Procedure statement. For example, the process used by the application named ORDERS can be named ORDERPROC; 1, ORDERPROC; 2, etc. The Drop Procedure ORDERPROC statement will remove the entire group. If the name is included in the name, the number should not be included in the identifier, and should only use the appropriate delimiter before and after procedure_name.
@Parameter
Parameters in the process. One or more parameters can be declared in the CREATE Procedure statement. The user must provide the value of each declared parameter (unless defined the default value of the parameter) is defined. The stored procedure can have a maximum of 2.100 parameters.
Use the @ symbol as the first character to specify the parameter name. The parameter name must meet the rules of the identifier. The parameters of each process are only used for the process itself; the same parameter name can be used in other processes. By default, the parameters can only replace constants, and cannot be used instead of the name, column name, or other database object. See Execute for more information.
DATA_TYPE
The data type of the parameters. All data types (including Text, NTEXT, and Image) can be used as a parameter of the stored procedure. However, the CURSOR data type can only be used for Output parameters. If the specified data type is CURSOR, you must also specify the varying and output keywords. For more information on the data types provided by SQL Server, please refer to the data type.
Note There is no maximum number of restrictions on output parameters that can be a Cursor data type.
Varying
Specifies the result set as the output parameter (dynamically constructed by the stored procedure, the content can be changed). Only for cursor parameters.
DEFAULT
The default value of the parameters. If default is defined, you don't have to specify the value of the parameter to execute. The default must be a constant or NULL. If the process will use the LIKE keyword to this parameter, the default value can contain wildcards (%, _, [], and [^]). OUTPUT
Indicates that the parameters are returned to the parameters. The value of this option can be returned to exec [ute]. Use the OUTPUT parameter to return the information to the calling process. Text, NTEXT, and Image parameters can be used as the Output parameter. The output parameters using the Output keyword can be a cursor placeholder.
n
Indicates that you can specify a placeholder of 2.100 parameters.
{Recompile | Encryption | Recompile, Encryption}
Recompile indicates that SQL Server does not caches the process of the process, which will be recompiled at runtime. Use the Recompile option when using an atypical or temporary value without wreathiping an execution plan in the memory.
Encryption indicates an entry that contains Create Procedure statement text in the SQL Server encrypted SysComments table. Using Encryption prevents a process of copying the process as a SQL Server replication.
Description In the upgrade process, SQL Server uses encryption comments stored in SysComments using encryption comments in syscomments.
For replication
Specifies that you cannot perform a stored procedure created for copying on the subscriber. . The stored procedures created using the for Replication option can be used as a stored procedure filtering and can only be executed during replication. This option cannot be used with the With Recompile option.
AS
Specifies the operation to be executed.
SQL_STATEMENT
The transact-sql statement of any number and type to include during the process. But there are some restrictions.
n
It is to indicate that this process can contain a placeholder for multiple Transact-SQL statements.
Comment
The maximum size of the stored procedure is 128 MB.
User-defined stored procedures can only be created in the current database (except for the temporary process, the temporary process is always created in the TEMPDB). In a single batch, the CREATE Procedure statement cannot be used in combination with other Transact-SQL statements.
By default, the parameters can be empty. If the NULL parameter value is passed and this parameter is used in the Create or ALTER TABLE statement, and the column referenced in this statement does not allow NULL, SQL Server generates an error message. To prevent the NULL parameter values that are not allowed to use NULL, programming logic should be added to the process or use the default value for the column (using the Create or ALTER TABLE DEFAULT keyword).
It is recommended to explicit NULL or NOT NULL for each column in any CREATE TABLE or ALTER TABLE statement in the stored procedure, such as when a temporary table is created. ANSI_DFLT_ON and ANSI_DFLT_OFF options Control SQL Server as a column avatars (if there is no specified in the CREATE TABLE or ALTER TABLE statement). If a connection execution of a connection is different from the settings of these options with the connection to create the process, the list columns created for the second connection may have different air and exhibit different behavioral ways. If NULL or NOT NULL is declared for each column, you will create a temporary table for emptying for all connections to execute the stored procedure.
When you create or change the stored procedure, SQL Server saves settings for Set Quoted_Identifier and SET ANSI_NULLS. These raw settings will be used when performing a stored procedure. Therefore, all client sessions will be ignored when the stored procedure is executed. The set quoted_identifier and set ANSI_NULLS statements that occur during the stored procedure do not affect the function of the stored procedure. Other SET options (such as SET ARITHABORT, SETDIARNINGS, or SET ANSI_PADDings) are not saved when creating or changing stored procedures. If the logic of the stored procedure depends on a specific setting, a set statement should be added at the beginning of the process to ensure that the setting is correct. When performing a set statement from a stored procedure, this setting is only valid before the stored procedure is completed. Thereafter, the setting will return to the value when the stored procedure is called. This allows individual clients to set the desired options without affecting the logic of the stored procedure.
Note SQL Server is interpreted by an empty string as a single space or interprets the true empty string, controlled by a compatibility level setting. If the compatibility level is less than or equal to 65, SQL Server interprets the empty string as a single space. If the compatibility level is equal to 70, SQL Server interprets the empty string as an empty string. For more information, see sp_dbcmptlevel.
Get information about stored procedures
To display text used to create a process, execute sp_helptext in the database where the process is located, and use the process name as the parameters.
Note The stored procedures created using the Encryption option cannot be viewed using sp_helptext.
To display a report for the object reference to the process reference, use sp_depends.
To rename a process, use sp_rename.
Reference object
SQL Server allows the created stored procedure to reference objects that have not yet exist. Only syntax check is performed when you created. When executed, if there is no valid plan in the cache, the stored procedure is compiled to generate an execution plan. Only all objects referenced during the stored procedure are parsed during the compilation process. Therefore, if the correct stored procedure is referenced by the syntax, it can be successfully created, but it will fail during runtime because the object referenced does not exist. For more information, see Delayed Name Resolution and Compilation.
Delay name resolution and compatibility level
SQL Server allows the Transact-SQL stored procedure to reference non-existing tables when creating. This ability is called a delay name resolution. However, if the Transact-SQL stored procedure references the tables defined during the store, the compatibility level setting (settings to be set by executing sp_dbcmptlevel) is 65, the warning message is issued when creating. If the table referenced at runtime does not exist, the error message will be returned. For more information, see SP_DBCMPTLEVEL and Delay Name Resolution and Compilation.
Execute stored procedures
After successfully execute the Create Procedure statement, the process name will be stored in the Sysobjects system table, and the text of the Create Procedure statement will be stored in SysComments. When executed for the first time, the process will be compiled to determine the best access plan for retrieving data.
Use the Cursor data type parameters
The stored procedure can only use the Cursor data type for the OUTPUT parameter. If you specify a Cursor data type for a parameter, you must also specify a Varying and Output parameters. If you specify a Varying keyword for a parameter, the data type must be CURSOR and must specify the Output keyword.
Note The CURSOR data type cannot be bind to application variables through a database API (eg OLE DB, ODBC, ADO, and DB-library). Because the Output parameter must be bound first, the application can perform a stored procedure, so the stored procedure with the CURSOR OUTPUT parameter cannot be called through the database API. These processes can only be called through Transact-SQL batch, stored procedures, or triggers to call the CURSOR OUTPUT variable to the Transact-SQL partial Cursor variable. CURSOR output parameters
In the execution process, the following rules apply to the Cursor output parameters:
For only the cursor, the rows returned by the result of the cursor are just rows that are in or exceed the cursor position at the end of the stored procedure, for example:
A non-rolling cursor is opened on the 100-row result set of the RS in the process. The process extracts the result of the head 5 line of the RS. The process returns to its caller. Returning to the result set RS consists of the sixth to 100 lines of RS, and the cursor in the caller is in the first line of RS. For only the cursor, if the stored procedure is completed, the cursor is located in front of the first line, and the entire result set will return to the call batch, stored procedure, or trigger. When returned, the cursor will be in front of the first line. For only the cursor, if the stored procedure is completed, the position of the cursor exceeds the end of the last row, then returns the empty result set for calling batch, stored procedures, or trigger.
The empty result set is different from the null value.
For scrolling cursors, all rows in the result set are returned to call batch, stored procedures, or flip-flops when the stored procedure is executed. When returns, the cursor remains in the process of performing the location of the extraction during the process. For any type of cursor, if the cursor is turned off, the null value is transferred to batch, stored procedures, or triggers. If you assign a marker to a parameter, this cursor has never been opened, and this will happen.
Note The shutdown state has an impact only when it is returned. For example, the cursor can be closed during the process, then open the cursor, then return the result set of the cursor to the call batch, stored procedure, or trigger.
Temporary stored procedure
SQL Server supports two temporary processes: local temporary processes and global temporary processes. The local temporary process can only be used by the connection to create the process. The global temporary process can be used by all connections. The local temporary process is automatically removed at the end of the current session. The global temporary process is removed at the end of the last session using the process. Usually at the end of the session of the process.
Temporary process with # and ## naming, can be created by any user. After the creation process, the owner of the partial process is the only user who can use the process. Permissions to execute local temporary processes cannot grant other users. If a global temporary process is created, all users can access the process and permissions cannot be explicitly abolished. Only users with explicit CREATE Procedure permissions in the Tempdb database can also be explicitly created in the database (not named naming). You can grant or abolish the permissions during these processes.
Note Frequent use of temporary stored procedures to create contentions on system tables in TEMPDB, resulting in negative impact on performance. SP_EXECUTESQL is recommended instead. Sp_executesql is not stored in the system table, so this problem can be avoided.
Automatically perform stored procedures
One or more stored procedures can be automatically performed when SQL Server starts. These stored procedures must be created by the system administrator and executed as a background process under the SYSADMIN fixed server role. These processes cannot have any input parameters.
There is no restriction on the number of startup processes, but pay attention to each startup process takes up a connection. If multiple processes must be performed at startup, do not need parallel execution, you can specify a process as a startup process, allowing the process to call other processes. This takes only one connection. After the last database is restored at startup, the stored procedure is started. To skip the execution of these stored procedures, specify the startup parameters as the tracking mark 4022. If SQL Server is started at a minimum configuration, the stored procedure will not be executed. For more information, see Tracking Tags.
To create a startup store, you must log in as a member of the sysadmin fixed server role and create a stored procedure in the Master database.
Using sp_procoption:
Specify an existing stored procedure as a startup process. Stop execution of the SQL Server startup. View the list of all procedures performed when SQL Server starts.
Storage process nested
The stored procedure can be nested, that is, a stored procedure can call another stored procedure. When executed by the calling process, the nested level will increase, and after the end of the called process is completed, the nested level will be reduced. If the largest nested level is exceeded, the entire calling process chain failed. The @@ NestleVel function can be used to return the current nested level.
To estimate the compiled stored procedure size, use the following Performance Monitoring Counters.
Performance Monitor Object Name Performance Monitor Name SQLServer: Buffer Manager Cache Size (Page Number) SQLServer: Cache Manager Cache His Motion Cache Page Cache Object Count *
* Various categories can use these counters, including special SQL, prepared SQL, process, trigger, and the like.
For more information, see SQL Server: Buffer Manager objects and SQL Server: Cache Manager objects.
SQL_STATEMENT limit
In addition to SET Showplan_Text and SET Showplan_all (both statements must be only a statement in batch, any SET statement can be specified inside the stored procedure. The selected set option is valid during the execution of the stored procedure and then restores the original setting.
If other users want to use a stored procedure, the object names used by some statements must be limited by the object name of the object owner within the stored procedure. These statements include:
Alter Table Create INDEX CREATE TABLE All DBCC Statement Drop Table Drop Index Truncate Table Update Statistics
Authority
CREATE Procedure Permissions The default grants the Sysadmin fixed server role member and DB_OWNER and DB_DDLADMIN fixed database role members. SYSADMIN Fixed Server Role Members and DB_OWNER Fixed Database Roles Members You can transfer Create Procedure permissions to other users. The owner of the permissions to execute the stored procedure, the owner can set the execution permission for other database users.
Example
A. Using a simple process with complex SELECT statements
The following stored procedures returns all authors (providing names), published books and publishers. This stored procedure does not use any parameters.
USE PUBS
IF exissrs (Select Name from sysobjects
Where name = 'au_info_all' and type = 'p')
Drop Procedure au_info_all
Go
Create Procedure Au_Info_Alas
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.TITE_ID Inner Join Publishers P
ON T.PUB_ID = P.PUB_ID
Go
AU_INFO_ALL stored procedures can be performed by the following methods:
Execute au_info_all
- OR
EXEC AU_INFO_ALL
If the process is the first statement in the batch, you can use:
AU_INFO_ALL
B. Using a simple process with parameters
The following stored procedure returns only the designated author (providing name), a book, and a publishing house. This stored procedure accepts a value that is accurately matched with the passing parameters.
USE PUBS
IF exissrs (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.TITE_ID Inner Join Publishers P
ON T.PUB_ID = P.PUB_ID
Where au_fname = @firstname
And au_lname = @Lastname
Go
AU_INFO stored procedures can be performed by:
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 of', @lastname = 'DULL'
If the process is the first statement in the batch, you can use:
AU_INFO 'DULL', 'Ann'
- OR
AU_INFO @LastName = 'DULL', @firstname = 'ann'
- OR
AU_INFO @firstname = 'Ann', @lastname = 'DULL'c. Using a simple process with wildcard parameters
The following stored procedure returns only the designated author (providing name), a book, and a publishing house. This stored procedure matches the passable parameters, and if the parameters are not provided, the preset default value is used.
USE PUBS
IF exissrs (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.TITE_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 performed in a variety of combinations. Some combinations are listed below:
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%'
D. Using Output parameters
The Output parameter allows external processes, batch or plurality of Transact-SQL statements to access a certain value set during process execution. The following example creates a stored procedure (Titles_Sum) and uses an optional input parameter and an output parameter.
First, create a process:
USE PUBS
Go
IF exissrs (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
SELECT 'TITLE NAME' = Title
From titles
Where title Like @@ Title
SELECT @@ Sum = SUM (Price)
From titles
Where title Like @@ Title
Go
Next, the OUTPUT parameter is used to control the streaming language.
Note Output variables must be defined when creating a table and using this variable. The parameter name and variable name do not necessarily match, but the data type and parameter location must match (unless @@ sum = variable form).
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)))
The following 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. Using OUTPUT cursor parameters
The OUTPUT cursor parameter is used to transfer the local cursor of the stored procedure to the batch, stored procedures, or flip-flops.
First, create the following procedure, declare and open a cursor on the Titles table:
USE PUBS
IF exissrs (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, a batch of batch is performed, declare a local cursor variable, perform the above process to assign the cursor to the local variable, then extract 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
F. Using the with Recompile option
If the parameters provided for the process are not typical parameters, and new execution plans should not be cached or stored in memory, with Recompile clauses will help.
USE PUBS
IF exissrs (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. Using the Encryption option
WITH ENCRYPTION clause to hide the stored procedure for the user. The following example creates an encryption process, using the sp_helptext system stored procedure to get information about the encryption process, then try to get information about the process directly from the SysComments table.
IF exissrs (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 following is the result set:
The Object's Comments Have Been Encrypted.
Next, select the identification number and text of the encrypted storage process content.
Select C.ID, C.Text
From syscomments C Inner Join Sysobjects O
On C.ID = O.ID
Where .name = 'encrypt_this'
The following is the result set:
Note The output of the TEXT column is displayed in a single line. When executed, this information will appear in the same row with the ID column information.
ID TEXT
---------- -------------------------------------------------------------------------------------------------------------------------------- --------------------
1413580074 ?????????????????????????? e ?????????????????????????????????????? ??????????????????????????????????????????? ??????????
(1 row (s) affected)
H. Creating a user-defined system stored procedure
The following example creates a process that displays the table name and its corresponding index at all tables starting with EMP. If you do not specify a parameter, the process will return all tables (and indexes) starting with SYS.
IF exissrs (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 nname 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 .n @@ Table
Go
USE PUBS
EXEC SP_SHOWINDEXES 'EMP%'
Go
The following is the result set:
TABLE_NAME INDEX_NAME INDEX_ID
--------------------------------------------
Employee Employee_ind 1
Employee PK_EMP_ID 2
(2 row (s) affected)
I. Eligation use of delay name
The following example shows four processes and delayed name parsing. Although referenced tables or columns are not present, each stored procedure can be created.
IF exissrs (Select Name from sysobjects
WHERE Name = 'proc1' and type = 'p')
Drop Procedure Proc1
Go
- Creating a procedure on a nonexistent table.
USE PUBS
Go
CREATE Procedure Proc1
AS
SELECT *
From does_not_exist
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 .name = 'proc1'
Go
Use master
Go
IF exissrs (Select Name from sysobjects
WHERE Name = 'proc2' and type = 'p')
Drop Procedure Proc2
Go
- CREANG A Procedure That Attempts To Retrieve Information from A
- Nonexistent column in an existing table.
USE PUBS
Go
CREATE Procedure Proc2
AS
Declare @middle_init char (1)
Set @middle_init = NULL
SELECT AU_ID, MIDDLE_INITIAL = @middle_init
From authors
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 .name = 'proc2'
