CODEWITHSUNDEEP
sql-server-2008t-sql
gunnerz
gunnerz

Reputation: 1926

GUID in dynamic sql

I am trying to use a column of type uniqueidentifier in Dynamic SQL.

I have a table X, with column ID of type guid.

If I do:

declare @primarykeyvalue varchar(50)  
set @primarykeyvalue = '648D2DD7-0EB1-4E29-A996-69456753C460'  

select * from X where ID = @primarykeyvalue

This works, but if I try to do the same in dynamic SQL like:

DECLARE @sql NVARCHAR(1000)   
SET @sql = 'select * from X where ID = ' + @primarykeyvalue  
EXECUTE(@sql)

This gives an syntax error as Incorrect syntax near 'D2DD7'.

Any suggestions please?

Upvotes: 1

Views: 3151

Answers (2)

Mithrandir
Mithrandir

Reputation: 25337

Change your code to:

DECLARE @sql NVARCHAR(1000)   
SET @sql = 'select * from X where ID = ''' + @primarykeyvalue + ''''  
EXECUTE(@sql)

Maybe you should think about using sp_executesql:

DECLARE @sql NVARCHAR(1000)   
SET @sql = 'select * from X where ID = @key'  

EXECUTE sp_executesql @sql, N'@key nvarchar(50)', @key = @primarykeyvalue

This enables you to use parameters in your queries and gets rid of the nasty doubled single quotes.

Upvotes: 6

marc_s
marc_s

Reputation: 754348

You need extra quotes around your GUID value:

DECLARE @sql NVARCHAR(1000)   
SET @sql = 'select * from X where ID = ''' + @primarykeyvalue + ''''

EXECUTE(@sql)

Upvotes: 3

Related Questions