Passing Arrays to Stored Proc

[ferronrsmith]

I want to pass an array of Id's to a stored proc to be processed, but i keep getting an error saying : Error converting data type varchar to numeric.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_getTranfer] @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, @ThisID varchar (10), @Pos 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)

Ferron

[Ashfield]

Your problem is that SQL Server does not allow an in clause to be a variable:

TRANSFER_ID IN (@TRANSFER_IDS)

for example will not work. What you need to do is split the variable into a table variable and join to that. Google has loads of examples

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP