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
———————————– Pass more then one parameter —————————————-
DECLARE @SQL_String NVARCHAR(max)
DECLARE @Parameter_Definition NVARCHAR(max)
SET @SQL_String = N’
EXEC GetEmail2 @EmployeeId = @EmployeeId_input, @Email = @Email_out OUTPUT
‘
SET @Parameter_Definition = N’
@EmployeeId_input uniqueidentifier,
@Email_out nvarchar(50) OUTPUT’
DECLARE @EmployeeId uniqueidentifier
DECLARE @Email nvarchar(50)
SET @EmployeeId = ‘997B3351-F876-414B-9C63-B90EC967B69B’
EXECUTE sp_executesql @SQL_String, @Parameter_Definition, @EmployeeId_input = @EmployeeId, @Email_out = @Email OUTPUT
SELECT @Email as Email
GO
