@
and that's not enclosed in quotation marks - for example, if you forget @
on a parameter name - the word is treated as an nvarchar string, in spite of the missing quotation marks.Term | Definition |
---|---|
RECOMPILE | Forces a new plan to be compiled, used, and discarded after the module is executed. If there is an existing query plan for the module, this plan remains in the cache. Use this option if the parameter you are supplying is atypical or if the data has significantly changed. This option is not used for extended stored procedures. We recommend that you use this option sparingly because it is expensive. Note: You can not use WITH RECOMPILE when calling a stored procedure that uses OPENDATASOURCE syntax. The WITH RECOMPILE option is ignored when a four-part object name is specified. Note: RECOMPILE is not supported with natively compiled, scalar user-defined functions. If you need to recompile, use sp_recompile (Transact-SQL). |
RESULT SETS UNDEFINED | Applies to: SQL Server 2012 (11.x) through SQL Server 2017, Azure SQL Database. This option provides no guarantee of what results, if any, will be returned, and no definition is provided. The statement executes without error if any results are returned or no results are returned. RESULT SETS UNDEFINED is the default behavior if a result_sets_option is not provided. For interpreted scalar user-defined functions, and natively compiled scalar user-defined functions, this option is not operational because the functions never return a result set. |
RESULT SETS NONE | Applies to: SQL Server 2012 (11.x) through SQL Server 2017, Azure SQL Database. Guarantees that the execute statement will not return any results. If any results are returned the batch is aborted. For interpreted scalar user-defined functions, and natively compiled scalar user-defined functions, this option is not operational because the functions never return a result set. |
<result_sets_definition> | Applies to: SQL Server 2012 (11.x) through SQL Server 2017, Azure SQL Database. Provides a guarantee that the result will come back as specified in the result_sets_definition. For statements that return multiple result sets, provide multiple result_sets_definition sections. Enclose each result_sets_definition in parentheses, separated by commas. For more information, see <result_sets_definition> later in this topic. This option always results in an error for natively compiled, scalar user-defined functions because the functions never return a result set. |
Term | Definition |
---|---|
{ column_name data_type [ COLLATE collation_name] [NULL | NOT NULL] } | See the table below. |
db_name | The name of the database containing the table, view or table valued function. |
schema_name | The name of the schema owning the table, view or table valued function. |
table_name | view_name | table_valued_function_name | Specifies that the columns returned will be those specified in the table, view or table valued function named. Table variables, temporary tables, and synonyms are not supported in the AS object syntax. |
AS TYPE [schema_name.]table_type_name | Specifies that the columns returned will be those specified in the table type. |
AS FOR XML | Specifies that the XML results from the statement or stored procedure called by the EXECUTE statement will be converted into the format as though they were produced by a SELECT .. FOR XML .. statement. All formatting from the type directives in the original statement are removed, and the results returned are as though no type directive was specified. AS FOR XML does not convert non-XML tabular results from the executed statement or stored procedure into XML. |
Term | Definition |
---|---|
column_name | The names of each column. If the number of columns differs from the result set, an error occurs and the batch is aborted. If the name of a column differs from the result set, the column name returned will be set to the name defined. |
data_type | The data types of each column. If the data types differ, an implicit conversion to the defined data type is performed. If the conversion fails the batch is aborted |
COLLATE collation_name | The collation of each column. If there is a collation mismatch, an implicit collation is attempted. If that fails, the batch is aborted. |
NULL | NOT NULL | The nullability of each column. If the defined nullability is NOT NULL and the data returned contains NULLs an error occurs and the batch is aborted. If not specified, the default value conforms to the setting of the ANSI_NULL_DFLT_ON and ANSI_NULL_DFLT_OFF options. |
EXEC
in this following statement is run, the database context is master.AS { LOGIN | USER } = ' name '
clause to switch the execution context of a dynamic statement. When the context switch is specified as EXECUTE ('string') AS <context_specification>
, the duration of the context switch is limited to the scope of the query being executed.AS { LOGIN | USER } = ' name '
must exist as a principal in sys.database_principals or sys.server_principals, respectively, or the statement will fail. Additionally, IMPERSONATE permissions must be granted on the principal. Unless the caller is the database owner or is a member of the sysadmin fixed server role, the principal must exist even when the user is accessing the database or instance of SQL Server through a Windows group membership. For example, assume the following conditions:EXECUTE @string_variable AS USER = 'CompanyDomainSqlUser1'
will fail because CompanyDomainSqlUser1
does not exist as a principal in the database.uspGetEmployeeManagers
stored procedure in the AdventureWorks2012 database expects one parameter (@EmployeeID
). The following examples execute the uspGetEmployeeManagers
stored procedure with Employee ID 6
as its parameter value.spGetWhereUsedProductID
stored procedure in the AdventureWorks2012 database. It passes two parameters: the first parameter is a product ID (819
) and the second parameter, @CheckDate,
is a datetime
value.EXECUTE
handles dynamically built strings that contain variables. This example creates the tables_cursor
cursor to hold a list of all user-defined tables in the AdventureWorks2012 database, and then uses that list to rebuild all indexes on the tables.uspGetEmployeeManagers
stored procedure on the remote server SQLSERVER1
and stores the return status that indicates success or failure in @retstat
.DEFAULT
keyword can be used.Proc_Test_Defaults
stored procedure can be executed in many combinations.SeattleSales
that points to another instance of SQL Server and executes a DDL statement (CREATE TABLE
) against that linked server.Proc_Test_Defaults
stored procedure and forces a new query plan to be compiled, used, and discarded after the module is executed.ufnGetSalesOrderStatusText
scalar user-defined function in the AdventureWorks2012 database. It uses the variable @returnstatus
to store the value returned by the function. The function expects one input parameter, @Status
. This is defined as a tinyint data type.SELECT
statements at the remote Oracle server. The example begins by adding the Oracle server as a linked server and creating linked server login.AS USER
clause to switch the execution context of the statement from the caller to User1
. The Database Engine will check the permissions of User1
when the statement is run. User1
must exist as a user in the database and must have permission to create tables in the Sales
schema, or the statement fails.?
) placeholder for a parameter. The example creates a linked server SeattleSales
that points to another instance of SQL Server and executes a SELECT
statement against that linked server. The SELECT
statement uses the question mark as a place holder for the ProductID
parameter (952
), which is provided after the statement.EXEC dbo.uspGetEmployeeManagers 6;
which returned 7 columns. The following example demonstrates using the WITH RESULT SET
syntax to change the names and data types of the returning result set.