CODEWITHSUNDEEP
sqlsql-serverquoting
EkeshOkor
EkeshOkor

Reputation: 109

SQL Server nested quotes - how to?

I've been looking for this for the past hour, but could not find any indication of this being possible.

In Oracle there is the ability to do nested quotes in the form of: 

Set @variable = q'[ *big sql block here* ]'

With this you didn't have to worry about escaping each string and row in a dynamic/static query or such.

But with SQL Server, i've searched and the only option available seems to be the double quotation/apostrophe.

Any ideas?

Appreciate it and apologize if this was asked before - but using my search parameters i could not find anything on this (maybe i'm just not using the correct jargon).

Upvotes: 2

Views: 4233

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

SQL Server does not have extended functionality like this. It uses the ANSI-standard mechanism of double single quotes.

If you are creating dynamic SQL, you might consider using parameters instead of embedding strings. So, instead of:

set @sql = 'select this from that where foo = ''bar''';

exec(@sql);

Use:

set @sql = 'select this from that where foo = @bar';
exec sp_executesql @sql, N'@bar varchar(255)', @bar='bar';

This might not meet all possible needs, but it could simplify your code and make it more readable.

Upvotes: 6

Related Questions