How to select rows from a table when having a list of GUIDs?

Question

I want to make a SELECT where I have multiple GUIDs that I use with an IN operator. Why this query does not return all rows where Table1UID equals each item from @table1IDs?

DECLARE @table1IDs NVARCHAR(100)
SET @table1IDs  = 'b15f554f-90ad-4fab-b3ac-a27bc68f3b21, 72213eb7-1041-408c-9ea1-35a667285c9b'

SELECT * FROM table1
WHERE Table1UID in (@table1IDs)

It only returns the row where ID is 'b15f554f-90ad-4fab-b3ac-a27bc68f3b21'.

Mention: type of Table1UID is UNIQUEIDENTIFIER

It is strange to have string-list of GUID-s - looks like wrong design. Why not simple ID-s (int)? — i486, Dec 18 '14 at 09:29
this query I want only for debugging purposes. We use GUID for primary keys. — Alexa Adrian, Dec 18 '14 at 09:35

Sarath Subramanian · Answer 1 · 2014-12-18T10:01:09.627

3

DECLARE @QRY NVARCHAR(MAX)

SET @QRY = 'SELECT * FROM table1 WHERE Table1UID IN (' + @table1IDs + ')'

EXEC SP_EXECUTESQL @QRY

UPDATE

If you are selecting dynamic values, use the below code and that will work

SELECT @table1IDs = COALESCE (@table1IDs + ',[' + YOURCOLUMN + ']', 
              '[' + YOURCOLUMN + ']')
               FROM    (SELECT DISTINCT YOURCOLUMN FROM YOURTABLE) PV  
               ORDER BY YOURCOLUMN

edited Dec 18 '14 at 10:01

answered Dec 18 '14 at 09:25

Sarath Subramanian

20,027
11
82
86

this solution is more useful. @alexa use variable for GUIDs list – robertw Dec 18 '14 at 09:31
Thanks. Your solution worked but when I tried to add another condition where I had another GUID column it returned me conversion issues. However it works as standalone, though hard to maintain. Thanks. – Alexa Adrian Dec 18 '14 at 09:55
If your values are dynamic and you are selecting the value from a column you can use the updated code @Alexa Adrian – Sarath Subramanian Dec 18 '14 at 10:04

score 3 · Accepted Answer · answered Dec 18 '14 at 09:26

3

The way you stored the value is wrong i guess, do as below

declare @temp table(ID nvarchar(1000))
insert into @temp values('b15f554f-90ad-4fab-b3ac-a27bc68f3b21')
insert into @temp values('72213eb7-1041-408c-9ea1-35a667285c9b')

SELECT * FROM table1
WHERE Table1UID in (select ID from @temp)

answered Dec 18 '14 at 09:26

knkarthick24

3,106
15
21

Thanks. I chose your solution cause you were the first. Solution of Reza ArabQaeni is almost the same. – Alexa Adrian Dec 18 '14 at 09:53

score 1 · Answer 3 · answered Dec 18 '14 at 09:36

1

DECLARE @table1IDs table(id uniqueidentifier)
insert into @table1IDs(id) values 
('b15f554f-90ad-4fab-b3ac-a27bc68f3b21')
,('72213eb7-1041-408c-9ea1-35a667285c9b')

select * from table1 where Table1UID in (select id from @table1IDs)

answered Dec 18 '14 at 09:36

Reza ArabQaeni

4,848
27
46

score -1 · Answer 4 · answered Dec 18 '14 at 09:40

Split the string into rows and use it in where clause

DECLARE @table1IDs NVARCHAR(100)
SET @table1IDs  = 'b15f554f-90ad-4fab-b3ac-a27bc68f3b21, 72213eb7-1041-408c-9ea1-35a667285c9b'


SELECT *
FROM   table1
WHERE  Table1UID IN(SELECT Rtrim(Ltrim(Split.a.value('.', 'VARCHAR(100)')))
                          FROM   (SELECT Cast ('<M>' + Replace(@table1IDs, ',', '</M><M>') + '</M>' AS XML) AS Data) AS A
                                 CROSS APPLY Data.nodes ('/M') AS Split(a))

How to select rows from a table when having a list of GUIDs?

4 Answers4