将ID数组传递给存储过程

I have stored procedure, i want to send it an array of values. When i send more than one i'm getting an error

我有存储过程,我想发送一个值数组。当我发送多个时,我收到错误

Error converting data type varchar to numeric.

将数据类型varchar转换为数字时出错。

Here is the code.

这是代码。

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_getTransfer] @TRANSFER_ID int = null, @COMPANY VARCHAR(5),@FORMTYPE VARCHAR(30), @TRANSFER_IDS VARCHAR(500) = NULL, @SEAL_DATE datetime = NULL AS
-- Declare variables
DECLARE @rc Int

IF @SEAL_DATE IS NULL
BEGIN 
SELECT 
COMPANY,
FORMTYPE,
COMPANY_NAME,
COMPANY_ADDRESS1,
COMPANY_ADDRESS2,
IMID,[DESCRIPTION],AMOUNT,AMOUNT_TRANSFER, 
CONSIDERATION,
TRANSFER_ID, 
[DATE],
CURR, 
CMF_NAME_1, 
CMF_NAME_2, 
CMF_ADDR_1, 
CMF_ADDR_2,
CMF_ADDR_3, 
CMF_ADDR_4, 
CMF_POSTAL_CODE,
Cons_Curr =  CASE
WHEN LTRIM(RTRIM(Cons_Curr)) <> '' THEN Cons_Curr
ELSE CURR
END,
-- Cons_Curr,
TRANSFERYEAR,
TRANSFERMONTH,
TRANSFERDAY,
dbo.CurrencyToWords(CURR) AS CURR_WORDS,
---dbo.CurrencyToWords(Cons_Curr) AS CONNS_CURR_WORDS

CONS_CURR_WORDS = CASE 
WHEN LTRIM(RTRIM(CONS_CURR)) <> '' THEN dbo.CurrencyToWords(CONS_CURR)
ELSE dbo.CurrencyToWords(CURR)
END



   FROM 
VW_TRANSFERS
   WHERE 
TRANSFER_ID= @TRANSFER_ID
AND 
FORMTYPE= @FORMTYPE
AND 
COMPANY = @COMPANY
OR 
TRANSFER_ID IN (@TRANSFER_IDS)
END

ELSE
SELECT 
COMPANY,
FORMTYPE,
COMPANY_NAME,
COMPANY_ADDRESS1,
COMPANY_ADDRESS2,
IMID,[DESCRIPTION],AMOUNT,AMOUNT_TRANSFER, 
CONSIDERATION,
TRANSFER_ID, 
[DATE],
CURR, 
CMF_NAME_1, 
CMF_NAME_2, 
CMF_ADDR_1, 
CMF_ADDR_2,
CMF_ADDR_3, 
CMF_ADDR_4, 
CMF_POSTAL_CODE,
--Cons_Curr,
Cons_Curr =  CASE
WHEN LTRIM(RTRIM(Cons_Curr)) <> '' THEN Cons_Curr
ELSE CURR
END,
TRANSFERYEAR,
TRANSFERMONTH,
TRANSFERDAY,
dbo.CurrencyToWords(CURR) AS CURR_WORDS,
---dbo.CurrencyToWords(Cons_Curr) AS CONNS_CURR_WORDS

CONS_CURR_WORDS = CASE 
WHEN LTRIM(RTRIM(CONS_CURR)) <> '' THEN dbo.CurrencyToWords(CONS_CURR)
ELSE dbo.CurrencyToWords(CURR)
END
   FROM 
VW_TRANSFERS
   WHERE 
FORMTYPE= @FORMTYPE
AND 
COMPANY = @COMPANY
AND
DATE = @SEAL_DATE 
OR 
TRANSFER_ID IN (@TRANSFER_IDS)








EXEC sp_getTransfer NULL, fgb, transfer, '124,444,4555,8865,24,55,69', NULL

2 个解决方案

#1

the following code cannot work:

以下代码无法工作:

TRANSFER_ID IN (@TRANSFER_IDS)

Several working alternatives described here:

这里描述了几种工作方案:

Arrays and Lists in SQL Server

SQL Server中的数组和列表

#2

you would have to set this up as a dynamic stored procedure or loop through the ids in code and call the stored proc each time. Do you know how to set up a dynamic procedure?

您必须将其设置为动态存储过程或循环访问代码中的ID并每次调用存储过程。你知道如何设置动态程序吗?

EXAMPLE

ALTER Procedure [dbo].[sp_NAME]
 (@id as varchar(max)
)
as
Declare @query1 as nvarchar(max) 
If @Param is null
set @Param = ''

Set @query1 = '
select whatever
from table
where id in('+@id+')'
EXECUTE sp_executesql @query1

#1