0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
James123
  • 11,184
  • 66
  • 189
  • 343
  • There is a lot of external context required on this. The generally acceepted method doing this is using a table valued parameter. I suggest you read this and apply one of the many solutions posed in this 5 year old question https://stackoverflow.com/questions/42448596/how-do-i-pass-a-list-as-a-parameter-in-a-stored-procedure – Nick.Mc Nov 10 '22 at 02:35
  • If you have any more details on this, i.e. is this being called by external code? Where do you get the list from? pleaase edit the question and add it – Nick.Mc Nov 10 '22 at 02:36
  • I am going pass .net string which has same format – James123 Nov 10 '22 at 02:38
  • Don't do that. Use a table valued parameter. Read this. https://gavilan.blog/2022/04/20/passing-a-list-of-values-to-a-stored-procedure-from-c/ – Nick.Mc Nov 10 '22 at 02:41
  • in side the sp query like this SELECT [MASTER_CUSTOMER_ID], USR_SPE_Membership_Status FROM CUSTOMER WHERE [MASTER_CUSTOMER_ID] IN ( @ip_master_customer_ids) – James123 Nov 10 '22 at 02:44
  • That code will not work in any way. Please read the article I posted. That is the correct way to do it. You'll need to change the way the proc works. Don't go down the path of concatenating strings etc. There is a known reliable way to do this using table valued parameters. – Nick.Mc Nov 10 '22 at 03:10
  • Does this answer your question? [How do I pass a list as a parameter in a stored procedure?](https://stackoverflow.com/questions/42448596/how-do-i-pass-a-list-as-a-parameter-in-a-stored-procedure) – Nick.Mc Nov 10 '22 at 03:11
  • problem with your solution is `CREATE TYPE IntegersList as Table (Id int);`. I don't think I am allowed to do that. – James123 Nov 10 '22 at 03:29
  • Please edit the question and add that detail, as well as whatever error message you are getting. So now you need to decide whether you want to get required access and do it properly or whether you want to introduce a substandard workaround in your solution – Nick.Mc Nov 10 '22 at 03:36
  • If you really do not want to try and get Table Valued Parameters working then you can try the `splitstring` method in the linked post https://stackoverflow.com/questions/42448596/how-do-i-pass-a-list-as-a-parameter-in-a-stored-procedure – Nick.Mc Nov 10 '22 at 03:48
  • What was the error you got when you tried `CREATE TYPE StringList as Table (Id nvarchar(50));`? Or are you saying this because someone told you you couldn't use that solution? – Nick.Mc Nov 10 '22 at 03:52
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/249466/discussion-between-james123-and-nick-mcdermaid). – James123 Nov 10 '22 at 04:21
  • @Nick.McDermaid I am doing this `SELECT [MASTER_CUSTOMER_ID], USR_SPE_Membership_Status FROM CUSTOMER WHERE [MASTER_CUSTOMER_ID] IN (SELECT [MASTER_CUSTOMER_ID] FROM @ListIds)` but SELECT is pulling entire `MASTER_CUSTOMER_ID` from the table. Not just requested. I don't whats wrong with it. – James123 Nov 10 '22 at 04:23
  • I posted my answer below - please give it a go – Nick.Mc Nov 10 '22 at 05:47
  • I see you are also trying the table valued parameter approach. If it is successful, please accept the answer in the other question and reference it here – Nick.Mc Nov 10 '22 at 05:55

3 Answers3

1

Use Table Valued Parameters. Literally designed for this use case

https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-ver16

planetmatt
  • 407
  • 3
  • 10
0

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.

Patrick Hurst
  • 2,086
  • 1
  • 3
  • 13
  • how will you do if have multiple single quotes with comma – James123 Nov 10 '22 at 03:30
  • The very same way! `'''value1'',''value2'''` – Patrick Hurst Nov 10 '22 at 03:34
  • In my opinion, the C# piece shouldn't even bother with single quotes. This can be handled in the stored proc. Just send a string like this "1,2,3,4" then in the stored proc convert them to strings (if necessary). This save a whole of uneccesary confusion and bother around single quotes – Nick.Mc Nov 10 '22 at 03:50
  • but MASTER_CUSTOMER_ID is VARCHAR type. thats why I am using single quotes – James123 Nov 10 '22 at 03:52
  • It doesn't matter until it actually gets used by the select statement. – Nick.Mc Nov 10 '22 at 04:10
0

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.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91