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

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 s开发者_运维百科tring.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.) ?

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)) + '%'

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.

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 )