How to pass single quote string to a stored procedure in SQL Server?-CodePudding

I am trying to send a string to a stored procedure which single quotes of multiple Id's. I am going use that string in IN clause in the stored procedure.

In order to test this I set string to like below but I am getting error. Help me out how to pass this to the stored procedure?

DECLARE @str1 AS  NVARCHAR(max)
SET @str1 = '3229622','4183229','3257553','3003673','3358312','0682773','4069249','0854620','4667379','0013862','1319623','3220826','3405633','0797654','3240120'

--print @str1
EXEC [GetMemberInfoAndMemberSubscriptions] @str1

I'm getting an error

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','

This is the stored procedure:

CREATE OR ALTER PROCEDURE [dbo].[GetMemberInfoAndMemberSubscriptions] 
    (@ip_master_customer_ids AS NVARCHAR(max))
AS
BEGIN
    SELECT 
        [MASTER_CUSTOMER_ID], USR_SPE_Membership_Status  
    FROM 
        CUSTOMER 
    WHERE 
        [MASTER_CUSTOMER_ID] IN (@ip_master_customer_ids)
END

And this is my C# code calling it:

string MemberNumbers = "'3229622','4183229','3257553','3003673','3358312','0682773','4069249','0854620','4667379','0013862','1319623','3220826','3405633','0797654','3240120'";

@Nick suggest to table valued parameter with link https://gavilan.blog/2022/04/20/passing-a-list-of-values-to-a-stored-procedure-from-c/

CREATE TYPE StringList AS TABLE (Id nvarchar(50));

What other workaround we have to implement this?

CodePudding user response：

If you want to express a single quote as a string in TSQL you would need to escape it, which is also a single quote. Confusing, I know.

DECLARE @string NVARCHAR(10) = '''apple'''
PRINT @string

The first and last ' delimit the string The second and third to last escape the following ' allowing them to become legal values in the string.

Now this does not mean this is how you solve the particular problem you describe, but it is how you pass a single quote in a string in TSQL.

CodePudding user response：

Referencing this doc here:

How do I pass a list as a parameter in a stored procedure?

This is what I suggest:

A. In your C# app, pass data like this

string MemberNumbers = '1234|324763|234879|23879476|238478|23872|23178'

This is a string that is pipe delimited

B. In your stored proc use the string split approach

create procedure GetMemberInfoAndMemberSubscriptions
    @ip_master_customer_ids varchar(8000)

    as

SELECT [MASTER_CUSTOMER_ID], USR_SPE_Membership_Status  FROM  
CUSTOMER WHERE [MASTER_CUSTOMER_ID] IN ( 
       Select Value from STRING_SPLIT( @ip_master_customer_ids, '|') 
       )

Note there are constraints on this, for example your list of id's can't exceed 8000 chars.