Learning the stored procedure of SQL Server-understanding the Stored Procedure syntax

Create procedure
Create a stored procedure. The stored procedure is a set of statements that can receive and return user-supplied parameters. You can create a process for permanent use, or temporary use in a session (Partial temporary process), or temporary use in all sessions (Global temporary process ). You can also create a stored procedure that runs automatically when Microsoft SQL Server is started.

Syntax
Create proc [edure] procedure_name [; number]
[{@ Parameter data_type}
[Varying] [= default] [Output]
] [,... N]

[
{Recompile | encryption | recompile, encryption}]

[For replication]

As SQL _statement [... n]

Parameters
Procedure_name

Name of the new stored procedure. The process name must comply with the identifier rules and must be unique to the database and its owner. For more information, see use identifiers.
To create a local temporary process, you can add a identifier (# procedure_name) before procedure_name. To create a global temporary process, you can add two identifier (# procedure_name) before procedure_name ). The complete name (including # Or #) cannot exceed 128 characters. The name of the specified process owner is optional.

; Number
Is an optional integer used to group processes with the same name, so that the same group of processes can be removed with a drop procedure statement. For example, the process used by an application named orders can be named orderproc; 1, orderproc; 2, and so on. The drop procedure orderproc statement removes the entire group. If the name contains a bound identifier, the number should not be included in the identifier and the appropriate delimiters should be used before and after procedure_name.

@ Parameter

Parameters in the process. One or more parameters can be declared in the create procedure statement. You must provide the value of each declared parameter during execution (unless the default value of this parameter is defined ). A 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 comply with the identifier rules. Each process parameter is only used for this process. The same parameter name can be used in other processes. By default, parameters can only replace constants, but cannot replace the names of table names, column names, or other database objects. For more information, see execute.

Data_type

The data type of the parameter. All data types (including text, ntext, and image) can be used as parameters for stored procedures. However, the cursor data type can only be used for output parameters. If the specified data type is cursor, the varying and output keywords must also be specified. For more information about the data types and syntax provided by SQL Server, see data types.

It indicates that there is no limit on the maximum number of output parameters that can be cursor data types.

Varying

Specify the result set supported by the output parameter (dynamically constructed by the stored procedure, and the content can be changed ). Only applicable to cursor parameters.

Default

The default value of the parameter. If the default value is defined, the process can be executed without specifying the value of this parameter. The default value must be a constant or null. If the like keyword is used for this parameter, the default value can contain wildcards (*, _, [], and [^]).

Output

Indicates that the parameter is a return parameter. The value of this option can be returned to Exec [ute]. The output parameter can be used to return information to the call process. The text, ntext, and image parameters can be used as output parameters. The output parameter using the output keyword can be a placeholder cursor.

N

It indicates that up to 2.100 parameter placeholders can be specified.

{Recompile | encryption | recompile, encryption}

Recompile indicates that SQL server does not cache the plan of the process, and the process will be re-compiled at runtime. Use the recompile option when you use an atypical or temporary value instead of overwriting the execution plan cached in the memory.

Encryption indicates the entries in the SQL Server encrypted syscomments table that contain the create procedure statement text. Encryption prevents the process from being published as part of SQL Server replication.

It indicates that during the upgrade, SQL server uses the encryption annotation stored in syscomments to recreate the encryption process.

For Replication

The storage process created for replication cannot be executed on the subscription server .. Stored Procedures created using the for replication option can be used for filtering stored procedures and can only be executed during replication. This option cannot be used with the with recompile option.

As

Specifies the operation to be performed in the process.

SQL _statement

Any number and types of transact-SQL statements to be included in the process. But there are some restrictions.

N

Yes indicates that this process can contain placeholders for multiple Transact-SQL statements.

Note
The maximum size of stored procedures is 128 MB.

User-Defined stored procedures can only be created in the current database (except for temporary procedures, temporary procedures are always created in tempdb ). In a single batch, the create procedure statement cannot be combined with other Transact-SQL statements.

By default, the parameter can be blank. 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 passing a null parameter value to a column that does not allow null, add the programming logic to the process or use the default value for the column (use the default keyword of create or alter table ).

We recommend that you explicitly specify NULL or not null for each column in any create table or alter table statement in the stored procedure, for example, when creating a temporary table. The ansi_dflt_on and ansi_dflt_off options control how SQL Server assigns the null or not null attribute to a column (if not specified in the CREATE TABLE or ALTER TABLE statement ). If the storage process executed by a connection has different settings for these options than the connection settings for the creation process, the table columns created for the second connection may be empty, and show different behavior methods. If null or not null is explicitly declared for each column, a temporary table will be created using the same NULL for all connections that execute the stored procedure.

When creating or changing a stored procedure, SQL Server saves the settings set quoted_identifier and set ansi_nulls. These original settings are used when the stored procedure is executed. Therefore, the set quoted_identifier and set ansi_nulls settings of all client sessions are ignored during the execution of the stored procedure. The Set quoted_identifier and set ansi_nulls statements that appear in the stored procedure do not affect the stored procedure function.

Other set options (such as set arithabort, set ansi_warnings, or set ansi_paddings) are not saved when the stored procedure is created or changed. If the Stored Procedure logic depends on specific settings, you should add a set statement at the beginning of the process to ensure that the settings are correct. When the set statement is executed from the stored procedure, this setting is only valid before the stored procedure is completed. Then, the setting will be restored to the value when the stored procedure is called. This allows individual clients to set the required options without affecting the Stored Procedure logic.

Whether SQL Server interprets a null string as a single space or as a real Null String, which is controlled by compatibility level settings. If the compatibility level is less than or equal to 65, SQL Server interprets the Null String as a single space. If the compatibility level is 70, SQL Server interprets the Null String as a null string. For more information, see sp_dbcmptlevel.

Obtain information about stored procedures
To display the text used to create a process, execute sp_helptext in the database where the process is located and use the process name as a parameter.

The stored procedure created using the encryption option cannot be viewed using sp_helptext.

To display reports on objects referenced by a process, use sp_depends.

To rename a process, use sp_rename.

Reference object
The stored procedure that SQL Server allows to reference an object that does not exist. During creation, only the syntax check is performed. If there is no effective plan in the cache during execution, compile the stored procedure to generate the execution plan. All objects referenced in the stored procedure are resolved only during compilation. Therefore, if a stored procedure with the correct syntax references a nonexistent object, it can still be created successfully, but it will fail during runtime because the referenced object does not exist. For more information, see latency name parsing and compilation.

Latency name resolution and Compatibility Level
SQL Server allows a stored procedure to reference a table that does not exist when it is created. This capability is called latency name resolution. However, if the transact-SQL stored procedure references the table defined in the stored procedure, and the compatibility level setting (set by executing sp_dbcmptlevel) is 65, A warning will be issued during creation. If the referenced table does not exist at runtime, an error message is returned. For more information, see sp_dbcmptlevel and latency name parsing and compilation.

Execute the Stored Procedure
After the create procedure statement is successfully executed, the process name is stored in the sysobjects system table, and the text of the create procedure statement is stored in syscomments. During the first execution, the process is compiled to determine the optimal data access plan.

Cursor data type parameters
Stored procedures can only use the cursor data type for output parameters. If you specify the cursor data type for a parameter, you must also specify the varying and output parameters. If the varying keyword is specified for a parameter, the data type must be cursor and the output keyword must be specified.

It indicates that the cursor data type cannot be bound to Application variables through database APIs (such as ole db, ODBC, ADO, and DB-library. Because the application can execute stored procedures only after the output parameter is bound, stored procedures with the cursor output parameter cannot be called through database APIs. Only when the cursor output variable is assigned to the transact-SQL local cursor variable can these processes be called through the transact-SQL batch processing, stored procedures, or triggers.

Cursor output parameters
The following rules apply to cursor output parameters during execution:

For a forward-only cursor, the rows returned in the result set of the cursor are only those that are at or beyond the cursor position when the execution of the stored procedure ends. For example:
Open a non-scroll cursor in the 100 rows result set named Rs during the process.

The first five rows of RS in the process extraction result set.

The process is returned to the caller.

The RS returned to the caller consists of rows 6th to 100 of Rs. The cursor in the caller is before the first row of Rs.
If a forward-only cursor is placed before the first row after the stored procedure is completed, the entire result set is returned to the call batch, stored procedure, or trigger. The cursor is located before the first row.

If the cursor position exceeds the end of the last row after the stored procedure is completed, an empty result set is returned for calling the batch processing, stored procedure, or trigger.

It indicates that the empty result set is different from the null value.

For a scrollable cursor, when the execution of a stored procedure ends, all rows in the result set are returned to call batch processing, stored procedure, or trigger. When the result is returned, the cursor is retained at the position of the last extraction in the process.

For any type of cursor, if the cursor is closed, the null value is passed back to the call for batch processing, stored procedures, or triggers. This can also happen if you assign a cursor to a parameter but the cursor has never been opened.

This indicates that the shutdown status is affected only when the response is returned. For example, you can close the cursor during the process, open the cursor later, and return the result set of the cursor to the calling batch processing, stored procedure, or trigger.

Temporary Stored Procedure
SQL Server supports two temporary processes: local and global. The local temporary process can only be used by the connection that creates the process. The global temporary process can be used by all connections. The partial temporary process is automatically removed when the current session ends. The global temporary process is removed when the last session of this process ends. Generally, the session is created at the end of the process.

The temporary process is named by # And #. It can be created by any user. After a process is created, the owner of a local process is the only user who can use the process. The permission to execute a local temporary process cannot be granted to other users. If a global temporary process is created, all users can access the process. Permissions cannot be explicitly revoked. Only users with explicit create procedure permissions in the tempdb database can explicitly create a temporary process in the database (without the name of the identifier ). Permissions in these processes can be granted or abolished.

It indicates that frequent use of the temporary stored procedure will result in contention on the system table in tempdb, thus negatively affecting the performance. We recommend that you use sp_executesql instead. Sp_executesql does not store data in the system table, so this problem can be avoided.

Automatic Execution of Stored Procedures
When SQL Server is started, one or more stored procedures can be automatically executed. These stored procedures must be created by the system administrator and executed as background procedures under the SysAdmin fixed server role. These processes cannot have any input parameters.

There is no limit on the number of startup processes, but note that each Startup Process occupies a connection during execution. If multiple processes must be executed at startup without parallel execution, you can specify a process as the startup process to call other processes. In this way, only one connection is occupied.

After the last database is restored at startup, the stored procedure starts. To skip the execution of these stored procedures, set the startup parameter to trace tag 4022. If SQL Server is started with the lowest configuration (with the-F flag), the stored procedure will not be started. For more information, see trace tag.

To create a stored procedure, you must log on as a member of the SysAdmin fixed server role and create a stored procedure in the master database.

You can use sp_procoption:

Specify an existing stored procedure as a startup process.

Stops the execution process when SQL Server is started.

View the list of all processes executed at SQL server startup.
Stored Procedure nesting
Stored procedures can be nested, that is, one stored procedure can call another stored procedure. When the called process starts to run, the nested level will increase. After the called process is executed, the nested level will decrease. If the maximum nesting level is exceeded, the entire call process chain will fail. The @ nestlevel function is available to return the current nested level.

To estimate the size of the compiled stored procedure, use the following performance monitoring counters.

Performance Monitor Object Name performance monitor counter name
Sqlserver: cache size of the buffer manager (number of pages)
Sqlserver: high-speed cache manager cache hit rate
Cache page
Cache object count *

* High-speed cache objects of various types can use these counters, including special SQL statements, preparation SQL statements, processes, and triggers.

For more information, see SQL server: buffer manager object and SQL Server: cache manager object.

SQL _statement restrictions
Except for set showplan_text and set showplan_all (these two statements must be the only statements in batch processing), any set statement can be specified within the stored procedure. The Set option is valid during the execution of the stored procedure and then restored to the original setting.

If other users want to use a stored procedure, the object names used by some statements must be limited by the name of the object owner. These statements include:

ALTER TABLE

Create Index

Create Table

All DBCC statements

Drop table

Drop Index

Truncate table

Update statistics
Permission
The create procedure permission is granted to SysAdmin fixed server roles and db_owner and db_ddladmin fixed database role members by default. SysAdmin fixed server role members and db_owner fixed database role members can transfer the create procedure permission to other users. Grant the owner of the stored procedure execution permission. The owner can set the execution permission for other database users.

Example
A. Simple process with complex select statements
The following stored procedure returns all authors (names provided), published books, and publishers from the join of the four tables. This stored procedure does not use any parameters.

Use pubs
If exists (Select name from sysobjects
Where name = 'au _ info_all 'and type = 'P ')
Drop procedure au_info_all
Go
Create procedure au_info_all
As
Select au_lname, au_fname, title, pub_name
From authors a inner join titleauthor Ta
On a. au_id = TA. au_id inner join titles t
On T. title_id = TA. title_id inner join publishers P
On T. pub_id = P. pub_id
Go

The au_info_all stored procedure can be executed in the following ways:

Execute au_info_all
-- Or
Exec au_info_all

If the process is the first statement in batch processing, you can use:

Au_info_all

B. Simple process with Parameters
The following stored procedure returns only the specified author (name provided), published books, and publishing house from the join of the four tables. The stored procedure accepts values exactly matching the passed parameters.

Use pubs
If exists (Select name from sysobjects
Where name = 'au _ info' and type = 'P ')
Drop procedure au_info
Go
Use pubs
Go
Create procedure au_info
@ Lastname varchar (40 ),
@ Firstname varchar (20)
As
Select au_lname, au_fname, title, pub_name
From authors a inner join titleauthor Ta
On a. au_id = TA. au_id inner join titles t
On T. title_id = TA. title_id inner join publishers P
On T. pub_id = P. pub_id
Where au_fname = @ firstname
And au_lname = @ lastname
Go

The au_info stored procedure can be executed in the following 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'

If the process is the first statement in batch processing, you can use:

Au_info 'dull', 'ann'
-- Or
Au_info @ lastname = 'dull ', @ firstname = 'ann'
-- Or
Au_info @ firstname = 'ann ', @ lastname = 'dull'

C. simple process of using parameters with wildcards
The following stored procedure returns only the specified author (name provided), published books, and publishing house from the join of the four tables. This stored procedure matches the pattern of the passed parameters. If no parameter is provided, the default value is used.

Use pubs
If exists (Select name from sysobjects
Where name = 'au _ info2 'and type = 'P ')
Drop procedure au_info2
Go
Use pubs
Go
Create procedure au_info2
@ Lastname varchar (30) = 'd *',
@ Firstname varchar (18) = '*'
As
Select au_lname, au_fname, title, pub_name
From authors a inner join titleauthor Ta
On a. au_id = TA. au_id inner join titles t
On T. title_id = TA. title_id inner join publishers P
On T. pub_id = P. pub_id
Where au_fname like @ firstname
And au_lname like @ lastname
Go

The au_info2 stored procedure can be executed in multiple combinations. Only 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. Use output parameters
The output parameter allows external processes, batch processing, or multiple Transact-SQL statements to access a value set during execution of the process. The following example creates a stored procedure (titles_sum) and uses an optional input parameter and an output parameter.

First, the creation process:

Use pubs
Go
If exists (Select name from sysobjects
Where name = 'titles _ Sum' and type = 'P ')
Drop procedure titles_sum
Go
Use pubs
Go
Create procedure titles_sum @ title varchar (40) = '*', @ sum money output
As
Select 'title name' = title
From titles
Where title like @ title
Select @ sum = sum (price)
From titles
Where title like @ title
Go

The output parameter is used in the control flow language.

The output variable must be defined when the table is created and used.

The parameter name and variable name do not have to match, but the data type and parameter location must match (unless in the form of @ sum = variable ).

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. Use output cursor Parameters
The output cursor parameter is used to pass the partial cursor of a stored procedure back to call a batch, stored procedure, or trigger.

First, create the following process and declare and open 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
Select *
From titles

Open @ titles_cursor
Go

Next, execute a batch process, declare a local cursor variable, execute the above process to assign the cursor to the local variable, and then extract 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

F. Use the with recompile Option
If the parameters provided for the process are not a typical parameter and the new execution plan should not be cached or stored in memory, the with recompile clause will be helpful.

Use pubs
If exists (Select name from sysobjects
Where name = 'titles _ by_author 'and type = 'P ')
Drop procedure titles_by_author
Go
Create procedure titles_by_author @ lname_pattern varchar (30) = '*'
With recompile
As
Select rtrim (au_fname) + ''+ rtrim (au_lname) as 'authors full name ',
Title as title
From authors a inner join titleauthor Ta
On a. au_id = TA. au_id inner join titles t
On ta. title_id = T. title_id
Where au_lname like @ lname_pattern
Go

G. Use the with encryption option
The with encryption clause hides the Stored Procedure text from the user. In the following example, create an encryption process and use the sp_helptext system stored procedure to obtain information about the encryption process. Then, try to obtain information about the process directly from the syscomments table.

If exists (Select name from sysobjects
Where name = 'encryption _ 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 ID and text of the stored procedure content.

Select C. ID, C. Text
From syscomments C inner join sysobjects o
On C. ID = O. ID
Where o. Name = 'encrypt _ this'

The following is the result set:

The output of the text column is displayed in a single row. This information appears in the same row as the ID column during execution.

Id text
----------------------------------------------------------------------
1413580074 ????????????????????????????????? E ??????????????????????????????????????? ???????????????????????????????????

(1 row (s) affected)

H. Create a user-defined system stored procedure
The following example creates a process that displays all tables whose names start with EMP and their corresponding indexes. If no parameter is specified, this process returns all tables (and indexes) whose names start with sys ).

If exists (Select name from sysobjects
Where name = 'SP _ showindexes' and type = 'P ')
Drop procedure sp_showindexes
Go
Use master
Go
Create procedure sp_showindexes
@ Table varchar (30) = 'sys *'
As
Select O. Name as table_name,
I. Name as index_name,
Indid as index_id
From sysindexes I inner join sysobjects o
On O. ID = I. ID
Where o. name like @ table
Go
Use pubs
Exec sp_showindexes 'emp *'
Go

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. Use delay name resolution
The following example shows the four processes and various possible use modes for delayed name resolution. Although the referenced table or column does not exist during compilation, each stored procedure can be created.

If exists (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 O. Name = 'proc1'
Go
Use master
Go
If exists (Select name from sysobjects
Where name = 'proc2 'and type = 'P ')
Drop procedure proc2
Go
-- Creating a procedure that attempts to retrieve information from
-- 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 O. Name = 'proc2'
Obtain information about stored procedures
To display the text used to create a process, execute sp_helptext in the database where the process is located and use the process name as a parameter.

The stored procedure created using the encryption option cannot be viewed using sp_helptext.
To display reports on objects referenced by a process, use sp_depends.
To rename a process, use sp_rename.
Execute the Stored Procedure

After the create procedure statement is successfully executed, the process name is stored in the sysobjects system table, and the text of the create procedure statement is stored in syscomments. During the first execution, the process is compiled to determine the optimal data access plan.