CODEWITHSUNDEEP
sql-server
goutham_kgh
goutham_kgh

Reputation: 167

Unable to escape single quotes in dynamic sql

I am executing a dynamic Sql snippet in SQL Server where the use case goes like this:

Take a Song ID (SongId) which is of an alphanumeric format (Eg: affgb:2c1) and extract some metadata from the Songs table from the Id.

In a normal case it would look like this:

SELECT *
FROM SongTable
WHERE SongId = 'affgb:2c1'

Due to some constraints I am using dynamic SQL which gets the song Id as a varchar type parameter called @songId. The query goes like this:

DECLARE @querySongFromId NVARCHAR(MAX);
SET @querySongFromId = 'SELECT * FROM SongTable WHERE SongId =' + @songId;
EXECUTE sp_executesql @querySongFromId;

The above query is failing as I am unable to insert the single quotes before the @songId

I tried escaping the single quotes the following way:

SET @querySongFromId = 'SELECT * FROM SongTable WHERE SongId = '' ' + @songId + ' '' ';

but I get an "Incorrect syntax near '=' " error.

Can someone kindly explain how to fix this ?

Upvotes: 1

Views: 155

Answers (1)

jthalliens
jthalliens

Reputation: 534

what about doing it this way:

DECLARE @querySongFromId NVARCHAR(MAX);
DECLARE @songId VARCHAR(100) = 'affgb:2c1'
SET @querySongFromId = 'SELECT * FROM SongTable WHERE SongId = @songId';
EXECUTE sp_executesql @querySongFromId,N'@songId VARCHAR(100)',@songId;

Upvotes: 4

Related Questions