April 9, 2002 at 2:17 pm
I'm writing a stored procedure, and I want one of the parameters to be used as a list of values to a select statement - the business end of the stored proc looks like:
SELECT * FROM table WHERE field IN(@param)
I want to call it using @param='''1'',''2'',''3''', but this doesn't work. It interprets it as IN(''1','2','3'') - comparing it to the whole value, rather than its component parts. Is there any way I can fix this?
April 9, 2002 at 3:06 pm
Only if you use dynamic sql or seperate each item to be used.
Ex.
DECLARE @SQLStr VARCHAR(400)
SET @SQLStr = 'SELECT * FROM table WHERE field IN(' + @param + ')'
EXEC (@SQLStr)
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 9, 2002 at 4:40 pm
I did something like that. But my string of possible in values may be up to 32K long. How can I deal with that?
April 9, 2002 at 5:13 pm
Set up multiple variables and set each to a section of code then do
EXEC (@SQLBase + @P1 + @P2)
You can have multiple 8000 character varchars at one time.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 9, 2002 at 5:31 pm
The problem I'm having is that the IN string is a parameter on the stored procedure. I'm calling the stored procedure from Access using ADO. What should I do? Create 10 parameters on the stored procedure?
April 9, 2002 at 5:37 pm
I am afraid that yes that would be the only choice I can see. And would have to parse those on the Access side. I will see if there is anything else around at work but I can't think of anything.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 10, 2002 at 2:46 am
What about creating a temporary table?
Prior to running the query create a temporary table into which you insert all the values in the @param field eg:
CREATE TABLE #TEMP_KEYS
(
KEY_FIELD nvarchar(x)
)....etc.....
CREATE INDEX ......KEY_FIELD .....etc....
Pseudo :-
For Each Key in @param
Insert Row into #TEMP_KEYS Where KEY_FIELD = Key
Next
Then Run your Select
SELECT *
FROM table A
,#TEMP_KEYS B
WHERE A.field = B.KEY_FIELD
Then :-
DROP TABLE #TEMP_KEYS
The overhead of the Insert into a Temporary Table may be too much, but at least it keeps the SQL Simple and efficient.
It's just an idea!
Simon Sutcliffe !MVP + !MSC
"To ask is human, to assume is dangerous"
April 10, 2002 at 8:35 am
I posted recently the Script that might help you with your problem:
Pass several sets of parameters to run stored proc
http://qa.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=232
Edited by - EPol29 on 04/10/2002 09:07:55 AM
April 10, 2002 at 9:23 pm
can your in clause be expressed as a Sql statement or are the values derived from somewhere else?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply