SQL Server Stored procedure parameter where IN (:parameter)

6mw9ycah 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(118)

I have here a stored procedure which expects two parameters

HeadID integer
KdNr nvarchar(100)

The select in the stored procedure looks like this:

select * 
from something 
where HeadID = @HeadID and KdNr in (@KdNr)

Now I execute the stored procedure like this

exec spTest 21, ('64303')

This works but if I add multiple customer numbers (they are integers in the database)

exec spTest 21, ('64303', '64304', '64305')

I get an error:

Failed to convert nvarchar to integer.

Can someone please help me / tell me how do I pass multiple integers to a stored procedure like this?

sql-server

来源：https://stackoverflow.com/questions/77286682/stored-procedure-parameter-where-in-parameter

1条答案

按热度按时间

b4lqfgs41#

Use the below function in your procedure.

CREATE FUNCTION [dbo].[SplitString]
(
    @Line nvarchar(MAX),
    @SplitOn nvarchar(5) = ','
)
RETURNS @RtnValue table
(
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Data nvarchar(100) NOT NULL
)
AS
BEGIN
    IF @Line IS NULL RETURN;

    DECLARE @split_on_len INT = LEN(@SplitOn);
    DECLARE @start_at INT = 1;
    DECLARE @end_at INT;
    DECLARE @data_len INT;

    WHILE 1=1
    BEGIN
        SET @end_at = CHARINDEX(@SplitOn,@Line,@start_at);
        SET @data_len = CASE @end_at WHEN 0 THEN LEN(@Line) ELSE @end_at-@start_at END;
        INSERT INTO @RtnValue (data) VALUES( SUBSTRING(@Line,@start_at,@data_len) );
        IF @end_at = 0 BREAK;
        SET @start_at = @end_at + @split_on_len;
    END;

    RETURN;
END;

The above function will split the string into each row.

Now use the above function in your procedure like: SELECT * FROM something WHERE HeadID = @HeadID AND KdNr IN (SELECT Data FROM dbo.SplitString(@KdNr, ',') WHERE Data <> '')

Now call your procedure like: exec spTest 21, '64303,64304,64305'

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Stored procedure parameter where IN (:parameter)

1条答案

相关问题

热门标签

最新问答