Stored procedure passing guids as CSV string

Question

Call of stored procedure

exec spBatchRequestsForRescreening @GuidList='''A2C4B17A-57A7-49A9-97A0-0000070D92F3'',''945FA383-17D4-4CCB-B8FD-00000A40E2DC'''

fails with "Conversion failed when converting from a character string to uniqueidentifier."

Stored procedure

ALTER PROC [dbo].[spBatchRequestsForRescreening] (@GuidList varchar(8000) )
AS BEGIN
    select *
    from DeniedPartyTransactions.dbo.DpsRequest
    with (nolock)
    where C1_PK in ( @GuidList )
END

Statement below works. Is there way for macro substitution of CSV of Guids in single quotes.

select * from DpsRequest
where c1_PK in ( 'A2C4B17A-57A7-49A9-97A0-0000070D92F3','945FA383-17D4-4CCB-B8FD-00000A40E2DC' )

Update:

Below does work. Maybe there is anything better ?

ALTER PROC [dbo].[spBatchRequestsForRescreening] (@GuidList varchar(7000) )
AS BEGIN
    DECLARE @SQL varchar(8000)
    SET @SQL = 
    'select *
    from DeniedPartyTransactions.dbo.DpsRequest
    with (nolock)
    where C1_PK in (' +  @GuidList + ')' ;
    EXEC(@SQL)    
END

score 2 · Answer 1 · edited May 23 '17 at 10:24

Create a split function:

CREATE FUNCTION dbo.SplitGUIDs(@List NVARCHAR(MAX))
RETURNS TABLE
AS
   RETURN ( SELECT Item = CONVERT(UNIQUEIDENTIFIER, Item) FROM
       ( SELECT Item = x.i.value('(./text())[1]', 'nvarchar(max)')
         FROM ( SELECT [XML] = CONVERT(XML, '<i>'
         + REPLACE(@List, ',', '</i><i>') + '</i>').query('.')
           ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
       WHERE Item IS NOT NULL
   );
GO

Then you can change your procedure this way:

ALTER PROCEDURE [dbo].[spBatchRequestsForRescreening] 
    @GuidList varchar(8000)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT d.* -- probably meant to list specific columns
      FROM dbo.DeniedPartyTransactions AS d
      INNER JOIN dbo.SplitStrings(@GuidList) AS s
      ON d.C1_PK = s.Item;
END
GO

(You won't have to include all the extra single quotes.)

You can also consider a table-valued parameter, you can see an example in my answer here:

Parameters to the EXISTS clause in a stored procedure

Stored procedure passing guids as CSV string

1 Answers1