Post reply

Dynamic "IN" or somehting better

  • 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

  • February 21, 2004 at 1:41 am

    #495038

    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)

  • February 21, 2004 at 1:57 am

    #495039

    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

  • February 21, 2004 at 11:57 am

    #495043

    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))

    next

    then in proc:

    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)

  • February 21, 2004 at 12:35 pm

    #495044

    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

     

     

  • February 21, 2004 at 2:42 pm

    #495046

    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)

  • February 21, 2004 at 4:14 pm

    #495049

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

     

    Jeremy

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

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