Passing an array of ID's to a stored procedure

Question

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.

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

Answer 1

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?

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

Answer 2

the following code cannot work:

TRANSFER_ID IN (@TRANSFER_IDS)

Several working alternatives described here:

Arrays and Lists in SQL Server