Using uniqueidentifier with IN Clause in SQL server

Question

I have a stored procedure that takes as an input a string of GUIDs and selects from table where table GUID IN (@Param).

@Param = 'b2e16cdc-1f1b-40e2-a979-f87a6a2457af,
c275dd13-bb54-4b8c-aa12-220b5980cabd,
af3552ec-37b1-4a76-81ad-1bd6b8c4cd6c,
3a7fda02-558b-49a9-a870-30350254d8c0,'
    SELECT * FROM dbo.Table1 WHERE 
    TableGUID IN (@Param)

However, I noticed that the query return values, only if the first GUID matches, otherwise it will not return anything. which means that it only compares with the first GUID in the string. anyone knows how solve the problem?

Why not using separate variable for all values and give the range like IN (@Param1, @Param2, @Param3...)? — vendettamit, Mar 12 '14 at 08:20
SQL, in common with most languages, when handed a **single** parameter that is a string, will not inspect the contents of the string, find commas, and decide to instead treat the *single* parameter as **multiple** parameters, perhaps even of a different data type. Why would you expect it to? — Damien_The_Unbeliever, Mar 12 '14 at 08:29
It would be better to define your stored procedure to accept a parameter of an appropriate data type, one that is **designed** to hold multiple separate values. There are two such types in SQL Server - table-valued parameters or xml. — Damien_The_Unbeliever, Mar 12 '14 at 08:31

score 2 · Answer 1 · answered Mar 12 '14 at 08:22

2

declare @sql varchar(max)
set @sql='SELECT * FROM dbo.Table1 WHERE 
    TableGUID IN ('+@Param+') '

exec (@sql)

answered Mar 12 '14 at 08:22

MikkaRin

3,026
19
34

Syed Tanvir Hossan · Answer 2 · 2019-10-19T05:36:48.500

We can't do it, because SQL has no concept of Lists, or array or other useful data structures - it only knows about tables (and table based information) so it converts the string list into a table structure when it compiles the command - and it can't compile a variable string, so it complains and you get annoyed. Or at least, I do.

What we have to do is convert the comma separated values into a table first. My initial version was inline, and rather messy, so I re-worked it to a user function and made it a bit more general purpose.

USE [Testing] GO

CREATE FUNCTION [dbo].[VarcharToTable] (@InStr NVARCHAR(MAX))
RETURNS @TempTab TABLE
   (id UNIQUEIDENTIFIER NOT NULL)
AS
BEGIN
    ;-- Ensure input ends with comma
    SET @InStr = REPLACE(@InStr + ',', ',,', ',')
    DECLARE @SP INT
    DECLARE @VALUE NVARCHAR(MAX)
    WHILE PATINDEX('%,%', @INSTR ) <> 0 
    BEGIN
       SELECT  @SP = PATINDEX('%,%',@INSTR)
       SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
       SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
       INSERT INTO @TempTab(id) VALUES (@VALUE)
    END
        RETURN

END
GO

This creates a user function that takes a comma separated value string and converts it into a table that SQL does understand - just pass it the sting, and it works it all out. It's pretty obvious how it works, the only complexity is the REPLACE part which ensures the string is terminated with a single comma by appending one, and removing all double commas from the string. Without this, while loop becomes harder to process, as the final number might or might not have a terminating comma and that would have to be dealt with separately.

DECLARE @LIST NVARCHAR(MAX)
SET @LIST = '973150D4-0D5E-4AD0-87E1-037B9D4FC03B,973150d4-0d5e-4ad0-87e1-037b9d4fc03c'
SELECT Id, Descr FROM TableA WHERE Id IN (SELECT * FROM dbo.VarcharToTable(@LIST))

score 1 · Answer 3 · answered Mar 12 '14 at 08:29

In addition to MikkaRin's answer: a GUID has to be unclosed in apostrophes, so the value in the parameter should look like 'b2e16cdc-1f1b-40e2-a979-f87a6a2457af', 'c275dd13-bb54-4b8c-aa12-220b5980cabd', 'af3552ec-37b1-4a76-81ad-1bd6b8c4cd6c', '3a7fda02-558b-49a9-a870-30350254d8c0'

In the end, you have to pass something like: @Param = '''b2e16cdc-1f1b-40e2-a979-f87a6a2457af'', ''c275dd13-bb54-4b8c-aa12-220b5980cabd'', ''af3552ec-37b1-4a76-81ad-1bd6b8c4cd6c'', ''3a7fda02-558b-49a9-a870-30350254d8c0'''

Pay attention to the last comma of the list. It should be removed.

This is also not working Getting error "Conversion failed when converting from a character string to uniqueidentifier. " — Vimal Patel, Jan 29 '17 at 17:44

Using uniqueidentifier with IN Clause in SQL server

3 Answers3