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
0 comments:
Post a Comment