• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

How Select Records with Multiple Unique Identifier?

Can someone give me some direction on how to retrieve records with multiple Unique Identifiers? Additionally, it checks if the records come back with a match, if not, set value to 0.

ex.

select sortno from toys where tid IN (@tid)

Within that statement, it would display sort number, but if there's no matching tid then it'll set the sortno value to 0.

Another question, it's not directly related to SQL. From the middle end, say ASP.NET, I'm trying to submit the unique identifiers into the query above through IN operator. but because, it's a uniqueidentifier, i can't add additional single quotes to it to retrieve the records. Does anyone know how I would submit the unique identifiers into the query to retrieve the records from the middle end? Thanks.
0
onL0oker
Asked:
onL0oker
  • 5
  • 3
  • 2
  • +1
2 Solutions
 
Kevin CrossChief Technology OfficerCommented:
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"
0
 
onL0okerAuthor Commented:
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.
0
 
Kevin CrossChief Technology OfficerCommented:
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
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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  

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
usage in your procedure
ALTER PROCEDURE yourprocedure (@tid varchar(8000))
AS
BEGIN
  select sortno from toys where tid IN (select value from dbo.ParmsToList(@tid, ',') )
END
 
and the procedure could be called like this:
 
exec yourprocedure 'A972C577-DFB0-064E-1189-0154C99310DAAC12,A973E577-EFB0-064E-1189-2256C99310FE1C13'

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Thx, Angel.  I didn't get a chance to type that out to explain.  That is exactly what I meant.
0
 
jrwaltbCommented:

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'

Open in new window

0
 
onL0okerAuthor Commented:
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.
0
 
Kevin CrossChief Technology OfficerCommented:
I believe it should work like this...
ALTER PROCEDURE yourprocedure (@tid varchar(8000))
AS
BEGIN
IF (select COUNT(sortno) from toys where tid IN (select value from dbo.ParmsToList(@tid, ','))) > 0
BEGIN
select COUNT(sortno) from toys where tid IN (select value from dbo.ParmsToList(@tid, ','))
END
ELSE
BEGIN
select 0 as sortno
END
END

Open in new window

0
 
onL0okerAuthor Commented:

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).
0
 
Kevin CrossChief Technology OfficerCommented:
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  

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now