Use of SQL SERVER: exceute statement: It is too powerful

The most common usage of an execute statement is to execute the stored procedure. However, he still has more powerful and extensive use methods.

1. Execute the Stored Procedure

Stored Procedure names support four object names: nominal value, variable

The passed parameters can be: literal value, variable, default value; input or output

Can obtain the return value of the stored procedure. If the stored procedure does not have a return statement, 0 is returned! If the stored procedure returns other types and other values, it must be a value that can be converted to the int type!

The same series of stored procedures can be executed.

Force re-compile the stored procedure during execution: With recompile

 

2. Execute the Function

Similar to executing a stored procedure.

Note: At this time, the function call cannot have parentheses ()!

Can obtain any scalar return type and value of the function, scalar!

 

3. Execute a dynamic SQL string

Execute statement must contain parentheses ()

SQL string can be any valid SQL string: literal value, variable

The execution context can be changed: As {login | user} = 'name'

 

4. pass-throug string: This function makes execute extremely convenient and powerful !!!!!!!!!!!!!!!!!!!!!!!!!

Similar to a dynamic SQL string, a string can contain "?" Indicates variables that can be transmitted to each other.

The replace variable can be the literal value or variable. Such as "? If output is of the output type, the replacement variable must be a variable.

After sqlsever2005, You can execute it on the connection server!

 

Execute syntax

Execute a stored procedure or function
[ {Exec | execute} ]
{
[@ Return_status =]
{Module_name [; number] | @ module_name_var}
[[@ Parameter =] {Value
| @ Variable [Output]
| [Default]
}
]
[,... N]
[With recompile]
}
[;]

Execute a character string
{Exec | execute}
({@ String_variable | [N] 'tsql _ string'} [+... n])
[As {login | user} = 'name']
[;]

Execute a pass-through command against a linked server
{Exec | execute}
({@ String_variable | [N] 'COMMAND _ string'} [+... n]
[{, {Value | @ variable [Output]} [... n]
)
[As {login | user} = 'name']
[AT _ SERVER_NAME]
[;]