June 3, 2002 at 2:23 am
I have a SP, which has a character input variable.
I want to use this variable as the IN expression for an integer field.
Example:
@Variable ='1,2,3,4'
CREATE PROCEDURE SP_Name
(@Variable varchar(255))
AS
Select * from table where field in (@Variable)
Like this i will get an error :
Syntax error converting the varchar value '1,2,3,4' to a column of data type int.
Edited by - toco on 06/03/2002 02:44:55 AM
June 3, 2002 at 4:56 am
Your field must be an int column which it is trying to match the case. However, the value when submitted via a single variable like this is treated as a single string not multiple items. Try this should help you get thru it, there are other ways but this works best for me.
@Variable ='1,2,3,4'
CREATE PROCEDURE SP_Name
(@Variable varchar(255))
AS
SET NOCOUNT ON
CREATE TABLE #tmpValues (
[val] [int] NOT NULL
)
WHILE CHARINDEX(',',@Variable) > 0 --Look for a , in string
BEGIN
INSERT INTO #tmpValues (val) VALUES (CAST(LEFT(@Variable, CHARINDEX(',',@Variable) - 1) AS INT)) --Insure or datatype
SET @Variable = RIGHT(@Variable,LEN(@Variable) - CHARINDEX(',',@Variable))
END
INSERT INTO #tmpValues (val) VALUES (CAST(@Variable AS INT))
Select * from table where field in (SELECT val FROM #tmpValues)
DROP TABLE #tmpValues
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 3, 2002 at 7:07 am
Thanks Lena
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply