How to use parameters in Stored procedure on in Clause

Question

I need to update a table by using Stored procedure.

In that Stored Procedure i am using a IN Clause for some specific rows,Here i use a string which is having a value exacltly like this ='AC101','AC102','AC103'

eg:

string Recipt = "'AC101','AC102','AC103'";

Also My stored procedured query is

@PaymentDate nvarchar(MAX),
@ReciptNo nvarchar(50)

AS
BEGIN

    SET NOCOUNT ON;
update Monthly_Payment set Payment_Date = @PaymentDate where Recipt_No in (@ReciptNo );

END

It's executing the query but not update the records which is mentioned in string

Note:

If i use normal query it's updated successfully.

ex:

update Monthly_Payment set Payment_Date = @PaymentDate where Recipt_No in (@ReciptNo );

Please update on this.

Possible duplicate of [Parameterize an SQL IN clause](https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) — Pred, May 04 '18 at 10:56
I don't recommend the answers in the linked thread. The top upvoted answers use Wild Cards and a `WHILE` loop. — Thom A, May 04 '18 at 11:04

score 1 · Answer 1 · answered May 04 '18 at 12:12

DECLARE @MonthlyPayment TABLE
(
  PaymentDate NVARCHAR(10) ,
  ReceiptNo NVARCHAR(50)
);

INSERT  INTO @MonthlyPayment
    ( PaymentDate, ReceiptNo )
VALUES  ( '2018-01-13', 'AC102' ),
    ( '2018-01-11', 'AC101' ),
    ( '2018-02-10', 'AC103' );

DECLARE @PaymentDate NVARCHAR(MAX)= '2018-05-04' ,
@ReceiptNo NVARCHAR(50)= N'AC101,AC102,AC103';


UPDATE  @MonthlyPayment
SET     PaymentDate = @PaymentDate
WHERE   ReceiptNo IN ( SELECT   value
                   FROM     STRING_SPLIT(@ReceiptNo, ',') ); 
/*The STRING_SPLIT function is available only under compatibility level   130. If your database compatibility level is lower than 130, SQL Server will    not be able to find and execute */
SELECT  PaymentDate ,
    ReceiptNo
FROM    @MonthlyPayment;

Faraz Babakhel · Answer 2 · 2018-05-04T12:35:26.407

0

Try this answer this will definitely work for you

Step 1 : first create this function. just run the following code

CREATE FUNCTION [dbo].[StringSplitToTable]  
    (  
      @Input NVARCHAR(MAX) ,  
      @Character CHAR(1)  
    )  
RETURNS @Output TABLE ( Item VARCHAR(500) )  
AS   
    BEGIN  
        DECLARE @StartIndex INT ,  
            @EndIndex INT  
        SET @StartIndex = 1  
        IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character   
            BEGIN  
                SET @Input = @Input + @Character  
            END  
        WHILE CHARINDEX(@Character, @Input) > 0   
            BEGIN  
                SET @EndIndex = CHARINDEX(@Character, @Input)  
                INSERT  INTO @Output  
                        ( Item  
                        )  
                        SELECT  SUBSTRING(@Input, @StartIndex, @EndIndex - 1)  
                SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))  
            END  
        RETURN  
    END

Step 2:

This Update query will be like the following

NOTE: MAKE SURE DATA SHOULD BE IN THIS FORMAT @ReciptNo='AC101,AC102,AC103'

update Monthly_Payment set Payment_Date = @PaymentDate where Recipt_No in (select item from StringSplitToTable(@ReciptNo,',') );

edited May 04 '18 at 12:35

answered May 04 '18 at 11:54

Faraz Babakhel

654
5
14

@Imthiyaz try this.. i hope this will work fine for you. Best of luck – Faraz Babakhel May 04 '18 at 11:55
It's still a `WHILE` loop. – Thom A May 04 '18 at 11:57
@Larnu While loop is just for convert string to table. and i am 101 percent sure this will work for Imthiyaz – Faraz Babakhel May 04 '18 at 12:01
I'm not saying it won't work, I'm saying don't use a `WHILE` loop. Use a set based solution for a set based environment. SQL Server is not a procedural environment. – Thom A May 04 '18 at 12:02
@Larnu And your set-based solution does look how? If you present some dynamic sql or string manipulation with percent signs, apostrophes, commas and like keywords, then this is not much better way. – Arvo May 04 '18 at 12:16
@arvo I've restored my prior answer for you. – Thom A May 04 '18 at 12:20
@Arvo i think this is the best possible solution – Faraz Babakhel May 04 '18 at 12:20
@Faraz Why? `WHILE` loops almost always perform poorly in SQL Server. If you're going to make a statement like that, please at least back it up. I'm not trying to be rude, I'd honestly like to see your facts behind such a statement. – Thom A May 04 '18 at 12:28
@Larnu if you read the question carefully Procedure get ReciptNo nvarchar(50) as a string not table type parameter. so that to convert string into table i use while loop. In your case Procedure receive ReciptNo as table. and you need to convert string into table at .cs file. both are fine but here mine answer is very relevant to the question. – Faraz Babakhel May 04 '18 at 12:33
That doesnt answer my question. Why is a `WHILE` loop better than a set based splitter? There have been numerous articles over the years to prove otherwise, so I'm interested to see your new information to prove otherwise. You've commented on my second answer, using the table type, but the first answer retains the `varchar` data type and uses a set based splitter, do I'd like to know why that answer is worse than yours. – Thom A May 04 '18 at 12:42
@Larnu If you can use `string_split()`, then of course this is acceptable solution - but only for late SQL server versions. I would not call that more set-based than Faraz function however - I'm pretty sure that internally it uses similar loops. Anyway, string splitting either way probably takes much less resources than subsequent select itself. – Arvo May 04 '18 at 13:00

How to use parameters in Stored procedure on in Clause

2 Answers2