October 31, 2003 at 11:43 am
I am trying to feed a comma separated list of integers into a function as an nvarchar(3000) and use it in an IN clause
The select statement tries to convert the nvarchar to int before running the select and gives an error
Syntax error converting the nvarchar value '21419, 21196' to a column of data type int
Is there a way to do this?
DECLARE @ERIDs NVARCHAR(3000)
DECLARE @Organization_IDs NVARCHAR(3000)
SET @Organization_IDs = '21419, 21196'
SELECT @ERIDs = ISNULL(@ERIDs + ', ', '') +Convert(nvarchar(20),ERID)
FROM ER
WHERE
Organization_ID IN (@Organization_IDs)
Print @ERIDs
Note, the preview removed the plus sign before the Convert?
October 31, 2003 at 11:51 am
October 31, 2003 at 7:30 pm
I had this same problem recently and the article that Jonathan linked to is where I found the best solution. A collegue had shown me a solution, but after I read the article is turned out to be the worst!
Anyhow, the best solution is to use a temporary table in your SP. The problem is that it sees @Organization_IDs as a single string instead of an array of integers. To fix this, push the integers into a temporary table and perform the IN clause against that table. Try the following code:
DECLARE @ERIDs nvarchar(3000)
CREATE TABLE #Organizaion_IDs
OrgID int
)
INSERT INTO #Organizaion_IDs (OrgID) VALUES (21419)
INSERT INTO #Organizaion_IDs (OrgID) VALUES (21196)
SELECT @ERIDs = ISNULL(@ERIDs + ', ', '') +Convert(nvarchar(20),ERID)
FROM ER
WHERE
Organization_ID IN (SELECT OrgID FROM #Organization_IDs)
Print @ERIDs
Let me know if this works.
Troy Tabor
Web Applications Project Manger
Choice Solutions LLC
October 31, 2003 at 8:54 pm
The article Jonathan linked to was perfect. The temp table solution is easy to use and works great.
thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply