CODEWITHSUNDEEP
sql-server
dhamo
dhamo

Reputation: 181

Conversion failed when converting from a character string to uniqueidentifier Error?

I would like to pass nvarchar values to uniqueidentifier.

Eg.:

Declare @test nvarchar(max);
set @test =  '''' + '77494371-30c1-4d2e-8dea-58dbefb325cc' + '''' --+ ',' + '''' + 'cb4229a2-76f8-4d68-aef7-f0bae089b382' + '''';
print @test;

Select * from Table1 where ID in (@test);

I tried to pass above condition. That time i am facing below error:

'77494371-30c1-4d2e-8dea-58dbefb325cc','cb4229a2-76f8-4d68-aef7-f0bae089b382'
Msg 8169, Level 16, State 2, Line 5
Conversion failed when converting from a character string to uniqueidentifier.

IF any way to pass Multiple uniqueidentifier values to Where In condition.

Please help me to solve this issue.

Upvotes: 0

Views: 777

Answers (2)

M.Ali
M.Ali

Reputation: 69504

Try this...

Declare @test nvarchar(max), @xml XML;
set @test =  '77494371-30c1-4d2e-8dea-58dbefb325cc,cb4229a2-76f8-4d68-aef7-f0bae089b382';

set @xml = N'<root><r>' + replace(@test,',','</r><r>') + '</r></root>'


Select * from Table1 
where ID in ( select r.value('.','varchar(max)') as item
              from @xml.nodes('//root/r') as records(r)
            );

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

Currently you query will be parsed as 

Select * 
from Table1 
where ID in ('''77494371-30c1-4d2e-8dea-58dbefb325cc','cb4229a2-76f8-4d68-aef7-f0bae089b382''')

Where your input '''77494371-30c1-4d2e-8dea-58dbefb325cc','cb4229a2-76f8-4d68-aef7-f0bae089b382''' is definitely not a Unique Identifier so you are getting that error

I will suggest you to go with the below approach

Declare @guid_col table(guid_col uniqueidentifier);

insert into @guid_col 
values('77494371-30c1-4d2e-8dea-58dbefb325cc'),
       ('cb4229a2-76f8-4d68-aef7-f0bae089b382')

Select * from Table1 where ID in(select guid_col from @guid_col)

or you need a split string function in which you need to split the comma separated values in @test variable and use it in Where clause. For info on split string function check the below link

Split strings the right way – or the next best way

Upvotes: 1

Related Questions