CODEWITHSUNDEEP
sql-servervisual-studio
Imthiyaz
Imthiyaz

Reputation: 3

How to use parameters in Stored procedure on in Clause

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.

Upvotes: 0

Views: 61

Answers (2)

Faraz Babakhel
Faraz Babakhel

Reputation: 664

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,',') );

Upvotes: 0

Sameer
Sameer

Reputation: 381

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;

Upvotes: 1

Related Questions