T-SQL: How to do this: select * from xy where uid in (@parameter)

Question

Question:

How to do this:

DECLARE @StateUID varchar(max)  

SET @StateUID = 'E8812237-2F3B-445E-8EEF-020E0B6F6A53, 66E57225-642F-45B5-8E5D-070F2D1CF99D, 751C615B-EB9C-4D25-955D-0E0EB3CD05A2' 


SELECT StateFullName, StateAbbrv, StateID
FROM tblStates 
WHERE StateUID IN ( @StateID )

Doing string.join as shown below doesn't help as well:

SET @StateUID = '''E8812237-2F3B-445E-8EEF-020E0B6F6A53'', ''66E57225-642F-45B5-8E5D-070F2D1CF99D'', ''751C615B-EB9C-4D25-955D-0E0EB3CD05A2'''

I've now moved it into dynamic SQL, where it works.

But this is extremely error-prone, annoying and time-consuming, so I wanted to ask whether there is any non-insane way of doing this (without temp tables, functions etc.) ?

score 3 · Accepted Answer · edited Jul 19 '11 at 09:09

3

In this case it seems you can use 'like'

DECLARE @QueryUIDs  varchar(MAX)
SET @QueryUIDs = '''E8812237-2F3B-445E-8EEF-020E0B6F6A53'', ''66E57225-642F-45B5-8E5D-070F2D1CF99D'', ''751C615B-EB9C-4D25-955D-0E0EB3CD05A2''' 

SELECT StateFullName, StateAbbrv, StateUID
FROM tblStates 
WHERE @QueryUIDs LIKE '%' + CAST(StateUID AS CHAR(36)) + '%'

edited Jul 19 '11 at 09:09

Stefan Steiger

78,642
66
377
442

answered Jul 19 '11 at 07:43

t-clausen.dk

43,517
12
59
92

Nice solution by problem inversion. Elegant and yet soooo simple. It lacked a cast to varchar. I added the details. Everybody else, note that @QueryUIDs is actually a stored procedure parameter, passed by JOIN(Parameters!in_location_new.Value, "' , '") – Stefan Steiger Jul 19 '11 at 09:17

score 1 · Answer 2 · edited May 23 '17 at 10:34

One option is to parse the comma delimited string into a subquery. The code below assumes that you can remove spaces from the @StateUID string and that StateID is a unique identifier:

DECLARE @StateUID varchar(max), @xml xml  

SET @StateUID = 'E8812237-2F3B-445E-8EEF-020E0B6F6A53,' + 
    '66E57225-642F-45B5-8E5D-070F2D1CF99D,' + 
    '751C615B-EB9C-4D25-955D-0E0EB3CD05A2' 

SET @xml = '<root><r>' + replace(@StateUID,',','</r><r>') + '</r></root>'

SELECT StateFullName, StateAbbrv, StateID
FROM tblStates 
WHERE StateID IN ( 
    SELECT
      CONVERT(uniqueidentifier, t.value('.','varchar(36)')) as [id]
    FROM @xml.nodes('//root/r') as a(t)
)

There are many great string splitting functions but using XML is my favorite.

This seems to operate on the same base as the like solution. I didn't try, but I bet it works, so +1 too. — Stefan Steiger, Jul 19 '11 at 09:20

score 0 · Answer 3 · answered Jul 19 '11 at 07:43

0

if you don't like temp tables and arrays, you can use more than one variable:

DECLARE @StateUID_1 varchar(max)  
DECLARE @StateUID_2 varchar(max)  
DECLARE @StateUID_3 varchar(max)  

SET @StateUID_1 = 'E8812237-2F3B-445E-8EEF-020E0B6F6A53'
SET @StateUID_2 = '66E57225-642F-45B5-8E5D-070F2D1CF99D'
SET @StateUID_3 = '751C615B-EB9C-4D25-955D-0E0EB3CD05A2' 


SELECT StateFullName, StateAbbrv, StateID
FROM tblStates 
WHERE StateUID IN ( @StateUID_1, @StateUID_2, @StateUID_3 )

answered Jul 19 '11 at 07:43

ypercubeᵀᴹ

113,259
19
174
235

Bad luck, because the number of UID's are variable (ad infinitum). I only chose state as example, it's actually going to be workplace uids. – Stefan Steiger Jul 19 '11 at 09:06
If it is a variable number of UIDs, then arrays or tables is the best solution (and not insane at all). – ypercubeᵀᴹ Jul 19 '11 at 09:11

T-SQL: How to do this: select * from xy where uid in (@parameter)

3 Answers3