Stored Procedure...seems pretty simple, but... 1

[KevinFSI]

I have a really simple query, for the sake of argument, let's say it looks exactly like this:

SELECT *
FROM myTable
WHERE myValue IN (1,2,3,4)

Ok, if I run that query in SQL Query Analyzer, it works perfectly fine, but as a stored procedure where "1,2,3,4" is a value that is passed to SQL I can't get it to work.

SELECT *
FROM myTable
WHERE myValue IN (@variable)

I get the following error:

Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value '1,2,3,4' to a column of data type int.

The obvious answer is in the data type, but if that's the case, why can I write it as a regular query and get the right answer? Kevin
slanek@ssd.fsi.com

 

[strantheman]

hi Kevin

I haven't run SQL server for a while, but if I remember IN clauses cannot contain local SQL variables.

where myValue in (@variable) is not allowed, something to do with the order queries are parsed. Variable is never evaluated ... or something.

Have you found a solution yet?

 

[JHoarty]

Looks like maybe an opportunity for a nested query like:

SELECT * FROM myTable
WHERE myValue IN (
SELECT DISTINCT myValue2
FROM myTable2) John Hoarty
jhoarty@quickestore.com

http://www.quickestore.com

 

[KevinFSI]

Stran,
No solution yet. I posted this question in the SQL Server forum (thread183-107676) but the guy that responded had no idea I was using a variable passed from a CF template, so his solution was a little abstract. I'm waiting to hear back from him again.

John,
I'm no stranger to nested queries, but I'm not seeing how it would benefit me in this situation. I need the user to be able to select one number or a list of numbers from my page. So, either way I need to use a list or come up with a different solution alltogether.

To both,
A possible solution may be to place a simple "IF" statement in the stored procedure. The value passed in is either going to be a single number 1-7 or all numbers (1,2,3,4,5,6,7) those are the only two options.

if @myVar = "1,2,3,4,5,6,7"
...write the query with hard coded values 1-7 (which I've verified works)

else
...do the query using the variable

Any thoughts? Suggestions? Kevin
slanek@ssd.fsi.com

 

[strantheman]

Im tapping a resource. Will get back to you Kevin.

 

[KevinFSI]

Cool. Thanks, because I got nothin' to go on here!!! Kevin
slanek@ssd.fsi.com

 

[strantheman]

Kevin

CREATE PROCEDURE doStuff
@myVar varchar(2000)
AS
DECLARE @sqlStr varchar(4000)
SET @sqlStr = 'SELECT * FROM myTable WHERE myCol IN ('
SET @sqlStr = @sqlStr + @myVar + ')'

EXECUTE sp_executesql @sqlStr
go

To run it, you could:
EXECUTE doStuff '1,2,3,4'

I like having friends in the bizzzz

good luck bro :-I

 

[KevinFSI]

Looks like it may do the trick. I'm headed out for the day, but I'll give it a shot in the AM. I'll let you know how it goes. Either way, thanks for the help!!! Kevin
slanek@ssd.fsi.com

 

[strantheman]

I got a star! thanks

 

[strantheman]

Hey Kevin, I forgot something:

Make sure your declare statement makes a unicode varchar, not the standard varchar.

DECLARE @sqlStr nvarchar(4000)

I left off the n in my above post. This is very important for international reasons apparantly.

 

[KevinFSI]

Looks like I'm in business. Thanks again. Kevin
slanek@ssd.fsi.com

 

[Guest_imported]

Does anyone knows how to create a dynamic combo boxX?

 

[iza]

?? why are you asking this here ? you take the risk not beeing answered ! you should have started another thread
but i suggest that you'd start your new thread in the javascript forum where it's more likely to receive an accurate answer ------
please review FAQ183-874 - this will help you to get the best out of tt
[ "you" is not someone in particular - don't take it too personnal ]