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

Advertisements

sp_executeSql and EXEC/EXECUTE in SQL- Server 2005 (Difference and comparison):

Yesterday evening one of my good friend and regular reader of my blog came to my home. He has observed that I have used EXEC somewhere in my articles and sp_executeSQL somewhere in my code so we have discussed pro and cons of both. After finishing discussion he insists me to write an article for the same.

Ok then let us move towards our core topic of the article.

As per my observation, people use EXEC statement more then sp_ExecuteSQL statement. In fact, you will get good performance with sp_ExecuteSQL and cherry on the ice-cream is sp_ExecuteSQL is more secure than EXEC especially when we talk about SQL-Injection attack.

In case of direct use of T-SQL commands, SQL-Server engine will re-use query plan and won’t generate new every time. But when you are using dynamic SP to run T-SQL command, SQL Server engine may not use same query plan and may create new again and again.

Let us think that we have one Employee table in one SP named EmpDetails, if you pass empID, it will return complete detail of that employee. In this case, we may use different empID every time, though query is same just parameter is different, SQL-Server may create new execution plan every time you call your SP. So, now this is the time we have to think about performance. As long as EXEC concern, you can’t pass parameter to it but in sp_ExecuteSQL you can pass parameter so this is also one of the benefit. Let us see the use of sp_ExecuteSQL practically.

–create one table

CREATE TABLE EmpDetails

(

ID INT NOT NULL,

EmpName VARCHAR(15) NOT NULL

)

–inserting the records in it.

INSERT INTO EmpDetails

SELECT 1,‘Ritesh’ UNION ALL

SELECT 2,‘Dharmesh’ UNION ALL

SELECT 3,‘Bihag’ UNION ALL

SELECT 4,‘Rajan’

GO

–CLEARING CACHE

DBCC FREEPOCCACHE

GO

–Example with SP_executeSQL

–note that query variable must be of

–ntext, nchar or nvarchar

DECLARE @EmpID INT

DECLARE @StrQuery NVARCHAR(500)

SELECT @StrQuery=‘SELECT * FROM EmpDetails WHERE ID=@eID’

EXEC SP_ExecuteSQL @StrQuery,N‘@eID INT’,1

EXEC SP_ExecuteSQL @StrQuery,N‘@eID INT’,3

–Example with EXEC

DECLARE @EmpID INT

DECLARE @StrQuery NVARCHAR(500)

SET @StrQuery=‘SELECT * FROM EmpDetails WHERE ID=2’

EXEC (@StrQuery)

go

DECLARE @EmpID INT

DECLARE @StrQuery NVARCHAR(500)

SET @StrQuery=‘SELECT * FROM EmpDetails WHERE ID=4’

EXEC (@StrQuery)

go

Reference: Ritesh Shah