SQL Server books online: execution of Stored Procedures

Execute

User-Defined Functions, system processes, user-defined stored procedures, or extended stored procedures that execute scalar values. It also supports execution of strings in the transact-SQL batch processing.

To wake up calling a function, use ExecuteStored_procedure.

Syntax

Execute the stored procedure:

[[Exec [ute] { [@ Return_status =] {Procedure_name [; number] | @ procedure_name_var } [[@ Parameter =] {value | @ variable [Output] | [Default] [,... N] [With recompile]

Execution string:

Exec [ute] ({@ string_variable | [N] 'tsql _ string'} [+... n])

Parameters

[[exec [ute] {< br> [@ return_status =] {procedure_name [; number] | @ procedure_name_var }< br> [[@ parameter =] {value | @ variable [Output] | [Default] [,... n] [with recompile]

Execution string:

Exec [ute] ({@ string_variable | [N] 'tsql _ string'} [+... n])

Parameters

Exec [ute] ({@ string_variable | [N] 'tsql _ string'} [+... n])

Parameters

@ Return_status

Is an optional integer variable that saves the returned status of the stored procedure. This variable must be declared in a batch, stored procedure, or function before being used in an execute statement.

When it is used to wake up a scalar value user-defined function, the @ return_status variable can be any scalar data type.

Procedure_name

The name of the stored procedure to be called. The process name must comply with the identifier rules. For more information, see use identifiers. Regardless ofCodeThe name of the extended stored procedure is always case sensitive.

You can execute the creation process in another database, as long as the user has this process or has the appropriate permissions to execute it in the database. You can execute the process on another server that runs Microsoft SQL Server, as long as the user has the appropriate permissions to use the server (Remote Access) and can execute the process in the database. If the server name is specified but the database name is not specified, SQL Server searches for the process in the user's default database.

; Number

Is an optional integer used to combine processes with the same name so that they can be removed using a drop procedure statement. This parameter cannot be used to extend the stored procedure.

In the same applicationProgramThe process used in is generally combined in this way. For example, orderproc; 1, orderproc; 2, and so on can be used in the ordering application. The drop procedure orderproc statement removes the entire group. A single process in a group cannot be removed after a process group is created. For example, drop procedure orderproc; 2 is not allowed. For more information about process groups, see create procedure.

@ Procedure_name_var

Is a local definition variable name, representing the name of the stored procedure.

@ Parameter

Is a process parameter, which is defined in the create procedure statement. The parameter name must be preceded by a symbol (@). In@ Parameter_name = ValueWhen the format is used, the parameter names and constants do not necessarily appear in the order defined in the create procedure statement. However, if there is a parameter that uses@ Parameter_name = ValueAll other parameters must use this format.

By default, the parameter can be blank. If the null parameter value is passed and this parameter is used for columns that are not allowed to be null in the create or alter table statement (for example, inserted into columns that are not allowed to be null ), SQL Server reports an error. To avoid passing the null parameter value to a column that is not allowed to be null, you can add the program design logic or use the default value (use the default keyword in the create or alter table statement) during the process ).

Value

Is the value of a parameter in the process. If the parameter name is not specified, the parameter value must be given in the order defined in the create procedure statement.

If the parameter value is an object name, string, or restricted by the database name or owner name, the name must be enclosed in single quotes. If the parameter value is a keyword, it must be enclosed in double quotation marks.

If the default value is defined in the create procedure statement, you do not need to specify a parameter when executing this process. If a parameter name with the like keyword is used in this process, the default value must be a constant and can contain wildcards such as %, _, [], and [^.

The default value can also be null. Generally, the Process Definition specifies the operation that should be performed when the parameter value is null.

@ Variable

Is a variable used to save parameters or return parameters.

Output

The specified stored procedure must return a parameter. The matching parameters of the stored procedure must also be created by the keyword output. This keyword is used when cursor variables are used as parameters.

If you use the output parameter to use its return value in other statements that call the batch processing or process, the parameter value must be passed as a variable (that is@ Parameter=@ Variable ).If a parameter is not defined as an output parameter in the create procedure statement, the process for specifying output for this parameter cannot be executed. You cannot use output to pass constants to stored procedures. Variable names are required for return parameters. Before executing the process, you must declare the Data Type of the variable and assign values. The return parameter can be any data type other than the text or image data type.

Default

The default value of the parameter is provided based on the process definition. An error occurs when the required parameter values do not have predefined default values, or the parameter is missing, or the default keyword is specified.

N

Is a placeholder, indicating that the project before it can be repeated multiple times. For example, the execute statement can specify one or more @Parameter, ValueOr @ variable.

With recompile

Forcibly compile a new plan. If the provided parameter is an atypical parameter or the data changes significantly, use this option. Use the changed plan in future program execution. This option cannot be used to extend the stored procedure. We recommend that you use this option as little as possible because it consumes more system resources.

@String_variable

Is the name of a local variable.@ String_variableYesChar,Varchar,NcharOrNvarcharData type. The maximum value is the available memory of the server. If the length of a string exceeds 4,000 characters, multiple local variables are concatenated for the execute string. For more information about the SQL server data types provided by the system, seeData Type.

[N]'Tsql_string'

Is a constant,Tsql_stringYesNvarcharOrVarcharData type. If n is included, the string is interpretedNvarcharData type. The maximum value is the available memory of the server. If the length of a string exceeds 4,000 characters, multiple local variables are concatenated for the execute string.

Note

If the first three characters of the process name are SP _, SQL Server searches for the process in the master database. If a valid process name cannot be found, SQL Server searches for the process whose owner name is DBO. To resolve the stored procedure name to a user-defined stored procedure with the same name as the system stored procedure, provide a completely legal process name.

You can useValueOr@ Parameter_name=ValueTo provide.The parameter is not a part of the transaction. Therefore, if the parameter value in the transaction is changed and the transaction is rolled back later, the parameter value will not be returned to the previous value. The value returned to the caller is always the value returned by the process.

Nesting is generated when one stored procedure calls another stored procedure. When the call process starts to be executed, the nested level will increase. When the call process ends, the nested level will decrease. The maximum number of nested links is 32. If the number of nested links exceeds 32, the entire call process chain fails. The current nesting level is stored in the @ nestlevel function.

SQL Server currently uses 0 to-14 returned values to indicate the execution status of stored procedures. Values-15 to-99 are reserved for use. For more information about the list of retained return Status values, seeReturn.

Because the remote stored procedure and extended stored procedure are not in the scope of the transaction (unless issued in the begin Distributed Transaction statement or used together with different configuration options ), therefore, the Command executed by calling cannot be rolled back. For more information, see system stored procedures and begin distributed transaction.

When a cursor variable is used, an error occurs if a cursor variable with a cursor is passed in the execution process.

When executing a stored procedure, if the statement is the first statement in the batch processing, you do not have to specify the execute keyword.

Execute Command with string

Use the String concatenation operator (+) to create a long string for dynamic execution. Each string expression can be a combination of Unicode and non-Unicode data types.

Although each [N]'Tsql_string'Or@ String_variableUp to 8,000 bytes are allowed. in SQL Server syntax analyzer, only logical processing is performed for this concatenation, without occupying physical memory. For example, this statement will never generate 16,000 concatenation strings:

Exec ('name _ of_8000_char_string '+ 'another _ name_of_8000_char_string ')

Statements in the execute statement are not compiled before execution.

Changes to the database environment are valid only before the execution statement ends. For example, after exec in this example, the database environment isMaster:

Use master exec ("use pubs") Select * from authors

Permission

The execute permission of a stored procedure is granted to the owner of the stored procedure by default. The owner can transfer this permission to other users. When an execute statement is encountered, even if the execute statement is stored, the permission to use the statement within the execute string is also checked. When running a stored procedure that executes a string, the system checks permissions in the user environment that executes the process, rather than in the user environment that creates the process. However, if a user has two stored procedures and the first procedure calls the second procedure, the execute permission check is not performed in the second procedure.

Example A. Use execute to pass a single parameter

ShowindParameters required for the stored procedure (@TabnameIs the name of a table. Run the following example:ShowindStored Procedure,TitlesIs the parameter value.

 

DescriptionShowindStored procedures are just used as an example,PubsThe database does not have this process.

 
Exec showind titles
Variables can be explicitly named during execution:Exec showind @ tabname = titles
 

If this isISQLIf the first statement is in a script or batch, the exec statement can be omitted:

 
Showind titles
-Or-Showind @ tabname = titles
 

B. Use multiple parameters and one output parameter
 
This example is executedRoy_checkStored Procedures, pass three parameters. Third Parameter @PCIs the output parameter. After the process is completed, the returned variables can be@Percent
.
 
 
 
 
 
 
DescriptionThe stored procedure is only used as an example,PubsThis process does not exist in the database.
 

  
 
 
 
Declare @ percent intexecute roy_check 'bu1032', 1050, @ Pc = @ percent outputset percent = @ percent
 
C. Execute with a variable' Tsql_string 'Statement
 
This example shows how the execute statement processes dynamically generated strings containing variables. This example is createdTables_cursorCursor to save all user-defined tables

(Type= U.
 
 
 
 
 
DescriptionThis example is only used as an example.
 

  
  
 
   
 
    
 
     
Declare tables_cursor cursor

For

Select name from sysobjects where type = 'U'

Open tables_cursor

Declare @ tablename sysname

Fetch next from tables_cursor into @ tablename

While (@ fetch_status <>-1)

Begin

/* A @ fetch_status of-2 means that the row has been deleted.

There is no need to test for this because this loop drops all

User-Defined tables .*/.

Exec ('drop table' + @ tablename)

Fetch next from tables_cursor into @ tablename

End

Print 'all user-defined tables have been dropped from the database .'

Deallocate tables_cursor
 
    
 
   
 
  
D. Execute statement with remote Stored Procedure
 
In this example, the remote serverSqlserver1Run onCheckcontractStored Procedure, in@RetstatSave and return the status, indicating that the operation is successful or failed.
 
 


     
     
 
      
 
       
 
        
Declare @ retstat int
Execute @ retstat = sqlserver1.pubs. DBO. checkcontract '2017-56-4008'
 
       
 
      
 
     
 
E. Execute statement with extended stored procedure
The following example usesXp_mongoshellThe Extended Stored Procedure lists the directories of all files with the. exe extension.
 
 


     
     
 
      
 
       
 
        
Use master
Execute xp_cmdshell 'dir *. EXE'
 
       
 
      
 
     
 
F. Use the execute statement with a stored procedure variable
 
In this example, create a variable that represents the name of the stored procedure.
 


     
     
 
      
 
       
 
        
Declare @ proc_name varchar (30)
Set @ proc_name = 'SP _ Who'
Exec @ proc_name
 
       
 
      
 
     
 
 
G. Use the execute statement with default
 
 
In this example, a stored procedure is created. The first and third parameters are the default values. When this process is run, if no value is transferred or the default value is specified,

These default values are assigned to the first and third parameters. Note that the default keyword can be used in multiple ways.
 

    
    
 
     
 
      
 
       
Use pubs
If exists (Select name from sysobjects
Where name = 'proc _ calculate_taxes 'and type = 'P ')
Drop procedure proc_calculate_taxes
Go
-- Create the stored procedure.
Create procedure proc_calculate_taxes (@ P1 smallint = 42, @ P2 char (1 ),
@ P3 varchar (8) = 'car ')
As
Select *
From mytable
 
      
 
     
 
    
 
Proc_calculate_taxesStored procedures can be executed in multiple combinations:
 

  
  
 
   
 
    
 
     
Execute proc_calculate_taxes @ P2 = 'A'

Execute proc_calculate_taxes 69, 'B'

Execute proc_calculate_taxes 69, 'C', 'house'

Execute proc_calculate_taxes @ p1 = default, @ P2 = 'D'

Execute proc_calculate_taxes default, @ P3 = 'local', @ P2 = 'E'

Execute proc_calculate_taxes 69, 'F', @ P3 = default

Execute proc_calculate_taxes 95, 'G', default

Execute proc_calculate_taxes default, 'h', default

Execute proc_calculate_taxes default, 'I', @ P3 = default