Use Uniqueidentifier in dynamic SQL in SQL Server 2008/2005

Error Fix:

Msg 402, Level 16, State 1, Procedure SPIDTester2, Line 6

The data types varchar and uniqueidentifier are incompatible in the add operator.

 

We used to use uniqueidentifier so many times in our database, since it is one of the unique field in table, we may need to put it in dynamic SQL quite a few times but I have seen so many times that even seasoned developer don’t know how to use UniqueIdentifier in dynamic SQL, may be in Store procedure in SQL Server. This is the reason I tempted to write something for this topic.

Let us see it practically:

--create table for testing

if OBJECT_ID('IDTester') is not null drop table IDTester

create table IDTester

(

      ID uniqueidentifier default newid(),

      name varchar(20)

)

GO

 

--insert few records

insert into IDTester(name)

select 'Ritesh' union all

select 'Rajan' union all

select 'Bihag' union all

select 'Abhijit'

GO

 

--let us see what we come up with

select * from IDTester

GO

 

--create simple SP

Create proc SPIDTester

@ID uniqueidentifier

as

begin

      select * from IDTester where ID=@ID

end

GO

 

----I got '7F1D8BC8-48AA-437E-B19F-4ABD139AD5E5' for first record

----you may get something else as a ID of first records.

exec spidtester '7F1D8BC8-48AA-437E-B19F-4ABD139AD5E5'

GO

 

--let us create another SP with dynamic SQL but it will show us an error

Create proc SPIDTester2

@ID uniqueidentifier

as

begin

      declare @sql varchar(max)

      set @sql='select * from IDTester where ID=' + @ID

      exec (@sql)

end

GO

--if you will try to create above SP, you will be greeted with

--following error

--Msg 402, Level 16, State 1, Procedure SPIDTester2, Line 6

--The data types varchar and uniqueidentifier are incompatible in the add operator.

 

 

--you have to use sp_executeSQL to get rid of above error

--with additional parameter

create proc SPIDTester2

@ID uniqueidentifier

as

begin

      declare @sql nvarchar(max)

      set @sql='select * from IDTester where ID=@I'

      exec sp_executesql @sql,N'@I uniqueidentifier',@I=@ID

end

GO

 

--let us see whether SP actually works

exec spidtester2 '7F1D8BC8-48AA-437E-B19F-4ABD139AD5E5'

GO

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com