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

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
– i486, Commented Dec 18, 2014 at 9:29
this query I want only for debugging purposes. We use GUID for primary keys. — Alexa Adrian
– Alexa Adrian, Commented Dec 18, 2014 at 9:35

knkarthick24 · Accepted Answer · 2014-12-18 09:26:21Z

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, 2014 at 9:26

knkarthick24

3,24619 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alexa Adrian Over a year ago

Thanks. I chose your solution cause you were the first. Solution of Reza ArabQaeni is almost the same.

Sarath Subramanian · Accepted Answer · 2014-12-18 10:01:09Z

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, 2014 at 10:01

answered Dec 18, 2014 at 9:25

Sarath Subramanian

21.6k12 gold badges89 silver badges95 bronze badges

3 Comments

robertw Over a year ago

this solution is more useful. @alexa use variable for GUIDs list

Alexa Adrian Over a year ago

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.

Sarath Subramanian Over a year ago

If your values are dynamic and you are selecting the value from a column you can use the updated code @Alexa Adrian

Reza ArabQaeni · Accepted Answer · 2014-12-18 09:36:06Z

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, 2014 at 9:36

Reza ArabQaeni

4,91830 silver badges48 bronze badges

Comments

Pரதீப் · Accepted Answer · 2014-12-18 09:40:35Z

-1

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))

answered Dec 18, 2014 at 9:40

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Linked

Related