How to get many rows based on several uniqueidentifiers?

Question

I'm trying to make a select like this:

DECLARE @ContentIDs VARCHAR(MAX);

SELECT @ContentIDs = 'e28faa48-adea-484d-9d64-ba1e1c67eea3,8338A6DE-8CDF-4F52-99CE-62E2B107FF97'

SELECT * FROM [Content] WHERE [ID] IN (@ContentIDs)

It only returns the content with the first UNIQUEIDENTIFIER.

How can I retrieve all rows?

Answer 1

You could

1. use dynamic SQL to generate the query but would need to be sure the list was sanitised

2. use a split function to parse the list and add each entry as a row into a table variable which you then join on that or

3. use SELECT * FROM [Content] WHERE @ContentIDs like '%' + cast([ID] as varchar(36)) + '%' this will force a full scan but if you are matching many rows this might not matter. (Edit but if you are matching many rows the comparison string will likely be huge!)

Option 2 would generally be my preferred approach. Or you could use a CTE to do something similar (Based on approach here)

DECLARE @ContentIDs VARCHAR(MAX);

SET @ContentIDs = 'e28faa48-adea-484d-9d64-ba1e1c67eea3,8338A6DE-8CDF-4F52-99CE-62E2B107FF97';

WITH Guids(pn, start, [stop]) AS
(
      SELECT 1, cast(1 as bigint), CHARINDEX(',', @ContentIDs)
      UNION ALL
      SELECT pn + 1, [stop] + 1, CHARINDEX(',', @ContentIDs, [stop] + 1)
      FROM Guids
      WHERE [stop] > 0
)

SELECT <collist> FROM 
[Content] 
WHERE [ID] IN (
    SELECT 
    CAST(LTRIM(RTRIM(SUBSTRING(@ContentIDs, start, 
       CASE WHEN [stop] > 0 
            THEN [stop]-start 
            ELSE LEN(@ContentIDs) END))) AS UNIQUEIDENTIFIER)
    FROM Guids where  [stop] > 0 or start>1
    )

Answer 2

You can do a string comparision with the comma-separated list:

SELECT * 
FROM [Content] 
WHERE ',' + @ContentIDs + ',' LIKE '%,' + CONVERT(varchar, [ID]) + ',%'

Answer 3

Try:

SELECT @SQL = 'SELECT * FROM [Content] WHERE [ID] IN (' + @ContentIDs + ')'
EXEC (@SQL)

Answer 4

That is because "IN" expects a table and you're giving it a string. Even with the "like" as suggested it would be very sloppy and your results won't be accurate at all.

You'd need something like the following to convert the string to a table for it to work:

CREATE FUNCTION [dbo].[split] (

    @sourcestring varchar(8000),
    @spliton varchar(1)
)

RETURNS @split table(value sql_variant)

AS  
BEGIN 

    while (charindex(@spliton,@sourcestring)>0)
        begin
            insert into @split
            select value = ltrim(rtrim(substring(@sourcestring,1,charindex(@spliton,@sourcestring)-1)))

            set @sourcestring = substring(@sourcestring,charindex(@spliton,@sourcestring) + len(@spliton),len(@sourcestring))
        end
    insert into @split select value = ltrim(rtrim(@sourcestring))

    RETURN

END

And then call it like this:

DECLARE @ContentIDs VARCHAR(MAX);

SELECT @ContentIDs = 'e28faa48-adea-484d-9d64-ba1e1c67eea3,8338A6DE-8CDF-4F52-99CE-62E2B107FF97'

SELECT * FROM [Content] WHERE [ID] IN (select value from dbo.split(@ContentIDs,','))