How Select Records with Multiple Unique Identifier?

In your ASP.NET application I would do this.

Dim tid As String = "'A972C577-DFB0-064E-1189-0154C99310DAAC12','A973E577-EFB0-064E-1189-2256C99310FE1C13'"
Dim sql As String = "select sortno from toys where tid IN (" & tid & ")"

If numeric, then just do this:
Dim tid As String = "1, 3, 4, 5, 7, 100"

Hi mwvisa1, I'm actually using parameters for SQL stored procedures. So the parameter tid is set as Char. Then it's sent to a stored procedure which the tid is set as unique identifier. I did exactly what you mentioned by setting it as a string and setting singles around the unique identifiers but it spit out an error stating can't convert tid from Char to Unique Identifier.

Yes, I was indicating to build the SQL statement dynamically on the ASP.NET side.  If you are passing in a parameter and the datatype of the TID field is uniqueidentifier, I don't believe you can get this to work using char for parameter type.  The parameter type will have to be at least a VARCHAR(36) if I am remembering correctly as you have 32 characters and 4 hyphens.  Therefore, if you change the parameter to be VARCHAR(MAX) or something like that then you can send multiple ids as I showed; however, you will have to split the string using a function on the comma OR use a technique saw here on EE where you change in statement to a like statement similar to this.

select sortno from toys where @tid like '%' + tid + '%'

or

select sortno from toys where charindex(tid, @tid) > 0

please create this function (usage in next post)

create FUNCTION dbo.ParmsToList (@Parameters varchar(500), @delimiter varchar(10) )
returns @result TABLE (Value varchar(8000))
AS  
begin
    declare @dx varchar(9)
    --declare @loops int
    -- set @loops = 0
 
     DECLARE @TempList table
          (
          Value varchar(8000)
          )
 
     if @delimiter is null  set @delimiter = ' '
     if datalength(@delimiter) < 1 set @delimiter = ' '
     set @dx = left(@delimiter, datalength(@delimiter)-1)
 
     DECLARE @Value varchar(8000), @Pos int
 
     SET @Parameters = @Parameters + @delimiter
     SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
 
     IF REPLACE(@Parameters, @delimiter, @dx) <> ''
     BEGIN
          WHILE @Pos > 0 --and @Loops < 100
          BEGIN
              -- set @loops = @loops + 1
               SET @Value = LEFT(@Parameters, @Pos - 1)
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @Parameters = SUBSTRING(@Parameters, @Pos+ datalength(@delimiter),500)
               SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
 
          END
     END    
     INSERT @result
     SELECT value
        FROM @TempList
     RETURN
END  

Select allOpen in new window

Thx, Angel.  I didn't get a chance to type that out to explain.  That is exactly what I meant.

Why the dynamic sql like this one did not work?

ALTER  PROCEDURE yourprocedure (@tid nvarchar(4000))
AS
BEGIN
 declare @sql as nvarchar(4000)
 set @sql = N'select sortno from toys where tid IN ('''+ @tid +''')' 
 
EXECUTE sp_executesql  @sql
END
 
 
Exec   yourprocedure '711DAF41-9998-45C2-8680-76323498C73D'',''BEAB4EE0-8E61-49B0-AA66-F7CBC9C34B70'

Select allOpen in new window

Hi visa and angel, the query works great. But additional question that I have regarding pulling non-existent sort numbers. Would it be possible to use case or conditional statement within the select statement to check rather the uid returned a match, if so, then select the sort number, if not, then give it a default value of 0? Thanks for the help.

Thanks visa, that works great. Do you know if the ids entered in a specified order retrieves the record base on the order of the ids submitted into the query? I have tried entering guids into different orders but it looks like it retrieves the results base on how the records were stored.

ie. guid 1, guid 2 -> retrieves record sort number 2 (guid 1), 1 (guid 2).

Using Angel's function and altering something like this (see code snippet).

Since you are selecting a count I am not sure why this matters, but you could do this with the updated function below:

SELECT value
FROM dbo.ParmsToList(@tid, ',')
ORDER BY Pos

create FUNCTION dbo.ParmsToList (@Parameters varchar(500), @delimiter varchar(10) )
returns @result TABLE (Pos int, Value varchar(8000))
AS  
begin
    declare @dx varchar(9)
    --declare @loops int
    -- set @loops = 0
 
     DECLARE @TempList table
          (
	  Pos int identity(1, 1), 
          Value varchar(8000)
          )
 
     if @delimiter is null  set @delimiter = ' '
     if datalength(@delimiter) < 1 set @delimiter = ' '
     set @dx = left(@delimiter, datalength(@delimiter)-1)
 
     DECLARE @Value varchar(8000), @Pos int
 
     SET @Parameters = @Parameters + @delimiter
     SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
 
     IF REPLACE(@Parameters, @delimiter, @dx) <> ''
     BEGIN
          WHILE @Pos > 0 --and @Loops < 100
          BEGIN
              -- set @loops = @loops + 1
               SET @Value = LEFT(@Parameters, @Pos - 1)
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @Parameters = SUBSTRING(@Parameters, @Pos+ datalength(@delimiter),500)
               SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
 
          END
     END    
     INSERT @result
     SELECT pos, value
        FROM @TempList
     RETURN
END  

Select allOpen in new window