WITH QUOTES clause
When you specify WITH QUOTES or WITH QUOTES ON, any double quotes in the string-expression are assumed to delimit an
identifier. When you do not specify WITH QUOTES, or specify WITH QUOTES OFF, the treatment of double quotes in the string-expression
depends on the current setting of the quoted_identifier option.
WITH QUOTES is useful when an object name that is passed into the stored procedure is used to construct the statement that
is to be executed, but the name might require double quotes and the procedure might be called when the quoted_identifier option
is set to Off.
WITH ESCAPES clause
WITH ESCAPES OFF causes any escape sequences (such as \n, \x, or \\) in the string-expression to be ignored. For example,
two consecutive backslashes remain as two backslashes, rather than being converted to a single backslash. The default setting
is equivalent to WITH ESCAPES ON.
One use of WITH ESCAPES OFF is for easier execution of dynamically constructed statements referencing file names that contain
In some contexts, escape sequences in the string-expression are transformed before the EXECUTE IMMEDIATE statement is executed. For example, compound statements are parsed before being
executed, and escape sequences are transformed during this parsing, regardless of the WITH ESCAPES setting. In these contexts,
WITH ESCAPES OFF prevents further translations from occurring. For example:
DECLARE String1 LONG VARCHAR;
DECLARE String2 LONG VARCHAR;
'SET String1 = ''One backslash: \\\\ ''';
EXECUTE IMMEDIATE WITH ESCAPES OFF
'SET String2 = ''Two backslashes: \\\\ ''';
SELECT String1, String2
WITH BATCH clause
The WITH BATCH clause allows you to control the execution of batches in EXECUTE IMMEDIATE statements. Setting WITH BATCH
OFF provides protection against inadvertent SQL-injection when the procedure is run.
WITH BATCH ON is the default, except for procedures owned by dbo.
When WITH BATCH OFF is used, the statement specified by string-expression must be a single statement.
WITH RESULT SET clause
The WITH RESULT SET clause allows the server to define correctly the procedure containing it. Specifying WITH RESULT
SET ON or WITH RESULT SET OFF affects both what happens when the procedure is created, as well as what happens when the procedure
is executed. The default option is WITH RESULT SET OFF.
You can have an EXECUTE IMMEDIATE statement return a result set by specifying WITH RESULT SET ON. With this clause, the containing
procedure is marked as returning a result set.
The EXECUTE IMMEDIATE statement extends the range of statements that can be executed from within procedures and triggers.
It lets you execute dynamically prepared statements, such as statements that are constructed using the parameters passed in
to a procedure.
Literal strings in the statement must be enclosed in single quotes. String literals cannot span multiple lines.
Only global variables can be referenced in a statement executed by EXECUTE IMMEDIATE.
Only Syntax 2 can be used inside Transact-SQL stored procedures and triggers.
Statements executed with EXECUTE IMMEDIATE do not have their plans cached.
EXECUTE IMMEDIATE is optional SQL language feature B031, "Basic dynamic SQL", of the SQL/2008 standard. The execute-option syntax is a vendor extension. The SQL/2008 standard prohibits the use of EXECUTE IMMEDIATE that returns a result set.
Syntax 2 is the Transact-SQL dialect's syntax for EXECUTE IMMEDIATE. The execute-option syntax is not supported by Adaptive Server Enterprise.