如何在子句中的存储过程中使用参数

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'

在那个存储过程中我使用一个特定行的IN子句,在这里我使用一个字符串,其值非常像这样='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.

请更新一下。

2 个解决方案

#1

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;

#2

Try this answer this will definitely work for you

试试这个答案肯定会对你有用

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

第1步:首先创建此功能。只需运行以下代码即可

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'

注意:确保数据应该是这种格式@ ReciptNo ='AC101,AC102,AC103'

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

#1

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;

#2