Varchar conversion to Int in a IN clause

[pamplemousse]

Hi all,

I need to create a stored procedure that will return a result set based on criterias that are provided by the application we use.

So far what we have is a store procedure that generate an sql statement dynamicaly. But I don't like this approche.

CREATE PROCEDURE sp_myProc ( inClause1 varchar, inClause2 varchar )
AS
BEGIN
DECLARE @sql VARCHAR(2000)
SET @sql = 'SELECT * FROM myTable
WHERE fieldChar IN (@inClause1)
AND fieldInt IN (@inClause2)'
EXEC (@sql)
END

Then I call the sp like so:
EXEC sp_myProc '''Male'',''Female''','1,2,3,4'

It works fine but I'm looking for a way not to recontruct the sql statement each time and pass only the parameters.

The problem is when I tried creating the sp using the parameters I got explicit data conversion error.
Trying to cast '1,2,3,4' as int.

I tried creating a function as StringTokenizer in Java to return a table containing int but got the same problem. My function would return correct info when assigning a local( within the function to test it ) varchar set to '1,2,3,4' but when passed a value '1,2,3,4' it would not return any rows.

Thanks for your time

Sylvain

 

[Rhys666]

i don't know if this is a type but your example code won't work as is. You're not concatenating the input parameters into the SQL String you're using the parameter names @inClause1 and @inClause2 as string values. So the SQL statement that you'll try to execute will be

SELECT * FROM myTable
WHERE fieldChar IN (@inClause1)
AND fieldInt IN (@inClause2)

...off hand, try this...

CREATE PROCEDURE sp_myProc ( inClause1 varchar, inClause2 varchar )
AS
BEGIN
 DECLARE @sql VARCHAR(2000)
 SET @sql = 'SELECT * FROM myTable
            WHERE fieldChar IN (' + @inClause1 + ')
                  AND fieldInt IN (' + @inClause2 + ')'
 EXEC (@sql)
END

Hope it helps

Rhys
Buffy: Spike, what are you doing here, five words or less!
Spike: Out for a walk... bitch!

 

[Rhys666]

Sorry, didn't look at that then.

Hmmm, Not sure about that then. Is the number of integer values passed into the sproc known or is it an amount that can vary? If it's going to vary, how many integer values could be passed in, is there a realistic maximum? If you have an indeterminate number of input integer parameters, and an indeterminate maximum number of input integer parameters I'm not sure you can do this without the dynamic SQL.

Rhys
Buffy: Spike, what are you doing here, five words or less!
Spike: Out for a walk... bitch!

 

[pamplemousse]

Thanks Rhys,

You are correct my example would not work without the quote. It' a transcription error.

In your second reply you ask if it's going to be an infinite number. Yes it is :0(

But Maybe you can help me with something.

Why is it that when I test a function:

CREATE FUNCTION dbo.fn_StringToInt
(@String VARCHAR, @delim CHAR)
RETURNS @results TABLE
( val INT )
AS
BEGIN
/*
I Used these default when creating this function while changing the names of the params to @string to @string1 and
@delim to @delim1 AND uncommented this part.
DECLARE @String VARCHAR(8000),
@delim CHAR(1)
SET @delim = ','
SET @String = '1,2,3'
*/
DECLARE @tempString VARCHAR(8000),
@len INT,
@positDelim INT,
@value INT

SET @tempString = @String
SET @len = LEN(@tempString)

SET @positDelim = 1
SET @value = 0

WHILE (CHARINDEX( @delim,@tempString) <> 0 )

BEGIN
SET @positDelim = CHARINDEX( @delim,@tempString)
SET @value = CAST(LEFT(@tempString,@positDelim -1) AS INT)
INSERT @results SELECT @value
SET @tempString = RIGHT(@tempString,@len - @positDelim)
SET @len = LEN(@tempString)
END
SET @value = CAST(LEFT(@tempString,@positDelim -1) AS INT)
INSERT @results SELECT @value
RETURN
END
GO
/*
THEN I would call the function like so
DECLARE @String3 VARCHAR(8000),
@delim3 CHAR(1)
SET @delim3 = ','
SET @String3 = '1,2,3'
SELECT * from dbo.fn_StringToInt(@String3,@delim3)
*/

Depending on if I used the parameters or the hard coded values within the fonction I got different results.

In the case I used the hard coded I got what I expected ( a table containing 1 2 3 ) and when I used the param @delim3 and @String3 I did not get anything back.

I'm confused ....

Thanks