Dynamic "IN" or somehting better

Question

Dynamic "IN" or somehting better

Jeremy Coulter

Valued Member

Points: 70
More actions
February 21, 2004 at 1:31 am

#86076

Hi all.

I am porting an Access DB over to MSDE.
I build an SQL Statement in code then pass that to the Database.
As part of the process, it builds an "IN" clause based on some check boxes the user selects, and this is then added the SQL variable which gets passed to the DB.

The problem I have is, I cant just pass the "IN" clause i.e.IN(1,2,3,4) as part of the StoredProc variables, because I cant go :-

WHERE     myfield='a'
AND         myotherfield in (@INVar)

I then thought maybe I need to pass each variable (of which there can be a max of 10) on its own, i.e.

@myvar1    int = null
@myvar2    int = null
@myvar3    int = null etc.etc.

Then use a CASE statement, i.e.
WHERE     myfield='a'
AND         myotherfield in (
                                         CASE WHEN @MyVar1 IS NOT NULL then 1,
                                         CASE WHEN @MyVar2 IS NOT NULL then 2,
                                        &nbsp

But this didnt seem to work either.

Is there a nice or even un-nice way I can do this other than a Dynamic SQL, because I had a problem with that too...hehe 😉

Thanks, Jeremy

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

chisholmd SSCrazy Points: 2490 More actions · Answer 1

I was just browsing another post where a guy/gal was having troubles passing a param to a job. Someone suggested having the code write the param to atable, then having the job read it from there.

I imagine the same solution would work here.

select where thisfield in (select params from sometable)

hmm that might not work but you could do it as an inner join instead and get the same results.

Im a little too tired to work it through any more right now, but if you really get stuck that might work for ya.

Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)

Jeremy Coulter Valued Member Points: 70 More actions · Answer 2

No this doesnt work unfortnitly.

I just realised I left out one small but crutuial poit, the "myotherfield" its looking to see the values are in is an INT

I did have a crack at your suggetion, but it failed becuase you are selecting out a string from the other table etc.

I CAN alwasy just alter my code so it will work with SQL Server, but things are so much nicer in stored procs 😀

Jeremy

chisholmd SSCrazy Points: 2490 More actions · Answer 3

Im not saying that this is the best solution but just for the fun of it I think it should work.

in code assuming an array of values for your IN statement:

for counter = 0 to ubound(inValues)

insert into constraintTable (keycol) value (inValues(counter))

SELECT desiredField from targetTable

INNER JOIN targetTable ON targetTable.inValueField= constraintTable.key

umm... my syntax might be off, query designer sure makes you lazy

that should work but of course your throwing away the performance gains of using a proc in the first place being running the inserts, so shouldn't bother, I imagine that it would be much better to simply use your dynamic sql in code:

SELECT desiredField from targetTable where inValueField in (" & list & ")

Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)

Jeremy Coulter Valued Member Points: 70 More actions · Answer 4

Well as it turned out, the solution is dead easy !

I put the same message on one of the MS news groups and someone replied to it with a good idea. Create 10 variables to be passed to the Stored Proc, and default them to null.

Then, in my IN clause just do :-

IN(@Type0,@Type1,@Type2,@Type3,@Type4, @Type5,@Type6,@Type7,@Type8,@Type9)

Then, if one of the vars is NULL it just puts NULL in the clause, i.e.

IN(1,2,NULL,4,NULL,6,7,8,NULL) etc.

This works perfectly, and is also, as a bonus, about a one line change of code in my app. too !

Jeremy

chisholmd SSCrazy Points: 2490 More actions · Answer 5

Great! Hmm i guess it doesn't match the nulls cause its not "IS NULL"

Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)

Jeremy Coulter Valued Member Points: 70 More actions · Answer 6

Yeah its def. one to put into the back of the mind 🙂

Jeremy