Building Strings Within Strings

When building strings for the EXECUTE() or sp_executesql() functions, it is commonly required to put a literal string inside a string. This can be accomplished in a number of different ways.

A common method is to use double quotes. If the SET QUOTED_IDENTIFIER option is set to off, double quotes can be used as a string delimiter. The default for this setting depends on context, but the preferred setting is ON, and it must be ON in order to use XQuery, indexed views and indexes on computed columns. However, SET commands within dynamic SQL only lasts for the block of dynamic SQL so SET QUOTED_IDENTIFIER can be referenced within the dynamic SQL.

For example,

DECLARE @MyString varchar(4000)

SELECT MyString = ‘SET QUOTED_IDENTIFIER OFF SELECT * FROM MyTable WHERE Name = “Jim”’
EXEC(@sql)

It is not recommended to use double quotes outside of the dynamic SQL statement (i.e. in the calling stored procedure) as they are not supported by default in many SQL editors, which can lead to difficulties in supporting existing stored procedures.

Another option is to use direct character references for the quote. While the double quotes may look easier to understand, the char(39) function provides the literal reference for the single quote and is supported by all editors.

For example,

DECLARE @MyString varchar(4000)
SELECT @MyString = ‘SELECT * FROM MyTable WHERE Name = ‘ + char(39) + ‘Jim’ + char(39) 
EXEC(@sql)

Lastly, the QUOTENAME() function can be used to return a string with quotes.

For example,

DECLARE @MyString varchar(4000)
SELECT @MyString = 'SELECT * FROM MyTable WHERE Name = ' + QUOTENAME('Jim','''')
SELECT @MyString