Dynamic IN Clause
-
I'm trying to dynamically create an IN clause to build into a stored procedure. I'm not getting any results when the variable is used in the IN clause. When I run it with explicit values there is data. The example below is simplifed for ease of conveying the problem. Any idea on what I'm doing wrong?
Thanks,MattDECLARE @SQL VARCHAR(20)SET @SQL = CHAR(39) + 'LMTO' + CHAR(39) + CHAR(44) + CHAR(39) + 'MMAG' + CHAR(39)--print @SQL--this works and returns 22 recordsselect * from fclty_que
where fclty_code IN ('LMTO','MMAG')
--this runs but doesn't return any dataselect * from fclty_que
where fclty_code IN (@SQL)
-
Porcrim,
There are plenty of articles/discussions here about this. The problem is that WHEN you pass a variable in to act as the dynamic IN... SQL treats it as a LITERAL and NOT as an ARRAY.
I would poke around with Remi's link and also search here (possibly even read yesterday's article about ARRAYS as well).... Should be able to find what you are looking for
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Your parameter @SQL becomes one string;
even though print @sql returns the value 'LMTO','MMAG', that value is stored in memory as the equivalent of "'LMTO'',''MMAG'", and does not return the results you are looking for.
Instead,
create table #Variable_list (sometext varchar(20)).
insert #Variable_list values ('LMTO')
insert #Variable_list values ('MMAG')
then change your code to
select * from fclty_que
where fclty_code IN (select sometext from #Variable_list)
-
When you use the "IN (@SQL)" syntax, you are literally looking for "fclty_code = @SQL" which is to say, fclty_code="'LMTO','MMAG'" so it does not find any records.
-
I do not know how much and where you are using this, but the easiest way to get a return would be:
DECLARE @SQL varchar(20)SET @SQL = CHAR(39) + 'LMTO' + CHAR(39) + CHAR(44) + CHAR(39) + 'MMAG' + CHAR(39)EXEC( 'SELECT * FROM fclty_que WHERE fclty_code IN ( ' + @SQL + ' )' )I wasn't born stupid - I had to study.
-
No, the easiest way is to create a split function that can return the list of codes. Then it can be reused everywhere else in the application... Also it avoids using dynamic sql.
-
I said easiest -not best.
(I agree with using a split function). I wasn't born stupid - I had to study.
-
I know that this thread could go on forever given the amount of times this is asked... Have you tried searching for array or split function from the search here? There is already a function that performs that here....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Some great feedback. Thanks to all!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply