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(),
–insert few records
insert into IDTester(name)
select ‘Ritesh’ union all
select ‘Rajan’ union all
select ‘Bihag’ union all
–let us see what we come up with
select * from IDTester
–create simple SP
Create proc SPIDTester
select * from IDTester where ID=@ID
—-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’
–let us create another SP with dynamic SQL but it will show us an error
Create proc SPIDTester2
declare @sql varchar(max)
set @sql=‘select * from IDTester where ID=’ + @ID
–if you will try to create above SP, you will be greeted with
–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
declare @sql nvarchar(max)
set @sql=‘select * from IDTester where ID=@I’
exec sp_executesql @sql,N’@I uniqueidentifier’,@I=@ID
–let us see whether SP actually works
exec spidtester2 ‘7F1D8BC8-48AA-437E-B19F-4ABD139AD5E5’
I am very happy and proud to be the part of TechED event held in Ahmedabad, Gujarat –India. I was disappointed when I was not able to go to TechED event held in Hydrabad – India but this Ahmedabad event remove my all disappointment.
Event was really overwhelmed by so many tech people not only from Ahmedabad city, not only from Gujarat state but people came over from different cities and states as well. It was really pleasure to meet so many technical people under one roof.
I was eager to meet both the SQL Server MVPs Mr. Pinal Dave (SQLAuthority.com) and Mr. Jacob Sebastian (BeyondRelational.com ) . Do you know what my bonus was? I met two industry leader Pinal and Jacob along with .NET MVP Kaushal Parikh. Isn’t is great to have three MVPs under one event in Ahmedabad, Gujarat? Yes, it is.
As long as technical session concern, I liked the speech of both the great speaker Pinal and Jacob and the way they have presented their topic with so much fun yet with great information. I have attended Pinal’s seminar in past but I attended Jacob’s speech first time ever in my life and I am really very impressed with his knowledge as a technocrat and very simple and kind hearted person as a human being. As long as Pinal concern, he is one of the great talented person and very co-operative by nature that I have felt so many time.
SQL Server cheet sheet was one of the attractions there along with the Query Flow chart. We can download cheet sheet from pinal’s personal site pinalDave.com but I guess there is no soft copy available for Query Flow chart. I would like to request Jacob and Pinal both to please upload soft copy of the same so whoever was not able to come to event, can have that amazing chart with them.
Now, let me tell you something about arrangement, this is also something I can’t stop myself to write about. It was really so impressive, once again Pinal and Jacob ran out of chair due to great response from the people and it has been arranged very quickly. Official starting time of event was 13:30, I reach there by 13:20 and you know what, I get chair in last row, people came there by 12:00PM to reserve chair, isn’t it HISTORICAL moment in history of Ahmedabad regarding any technical seminar? I guess it is.
Moreover, there were so many gifts given by organizer, I personally got four gifts for answering questions there.
In short, I really want to thanks everybody who has taken initiative to make this event successful and hope to see this kind of events again and again.
I don’t have much photograph so far but have only one right now when Pinal called me with him to give me one of the gifts. Have a look.