Professional Web Applications Themes

difficult sproc - Microsoft SQL / MS SQL Server

I have a requirement that I have to be able to handle many conditions on my parameters and pass that to a sproc. For instance, I could have a situation like this: LastName = "foo" OR LastName = "bar" AND City LIKE "blah" AND Age < 20 AND Age > 45 or LastName = "foo" OR City = "Blah" AND Age > 45 What is the best way to handle this in a sproc? I would rather not use dynamic SQL. I know if I have a something more simple, like LastName = "this" OR LastName = "that" OR LastName ...

  1. #1

    Default difficult sproc



    I have a requirement that I have to be able to handle many conditions on
    my parameters and pass that to a sproc. For instance, I could have a
    situation like this:

    LastName = "foo" OR LastName = "bar" AND City LIKE "blah" AND Age < 20
    AND Age > 45

    or

    LastName = "foo" OR City = "Blah" AND Age > 45

    What is the best way to handle this in a sproc? I would rather not use
    dynamic SQL.

    I know if I have a something more simple, like LastName = "this" OR
    LastName = "that" OR LastName = "other" that I can put that in an array
    and pass that parameter as a comma delimited string. From there stuff
    each delimted value into a temp table and Select...From...Where...In.

    This is a lot more complicated.

    Thanks,

    Bobby

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Bobby Guest

  2. #2

    Default Re: difficult sproc

    perhaps you could do something like:

    where lastname=isnull(lastname,lastname) and city=isnull(city,city)

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net



    "Bobby" <com> wrote in message
    news:phx.gbl... 


    oj Guest

  3. #3

    Default Re: difficult sproc



    Thanks for the link AJ. I have seen that page before. Where I guess I'm
    really getting stumped is with the placement of the AND's and OR's.

    This is a user interface for normal office workers to run reports. So a
    user may want to see a report where

    LastName ="value" OR City = "value" AND LastName = "value"
    or
    LastName >= 'Boggs' AND <= 'Maddox'
    or
    City Like 'Fresno'

    How can I handle all of these possible conditions without know what they
    will be? The users are not sql savvy enough to type it. So the GUI has
    to make it cute.

    I know I want to do this in a sproc (an alternative is to just select
    everything and apply the conditions to a DataView server side - but it
    is far too much data).

    Bobby

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Bobby Guest

  4. #4

    Default Re: difficult sproc

    If this is your requirement, then you will need to make the GUI smart enough
    to build the where clause (at least the part that is determined by the user)
    and pass it to your procedure. IMO

    "Bobby" <com> wrote in message
    news:phx.gbl... 


    Scott Guest

  5. #5

    Default Re: difficult sproc

    Bobby,

    Well the techniques are all there on that page.
    For a simplification without too much loss of functionality ( performance will suffer though)
    I would go for "AND" only, and rely on "LIKE" to allow the use enter "OR" conditions.

    where
    ....
    and Col1 like Col1Search
    and Col2 like Col2Search
    and Col3 like Col3Search
    ....

    Where ColX = '%' or whatever is passed, and give the users the list of Wildcard characters.

    A more user friendly adhoc query GUI is MS Access - You could create
    relevant views and allow connections from Access via the linked table option.
    They can then construct their own queries graphically.

    Or SQL isn't a difficult language - you could try teaching your users a select query ?

    Regards
    AJ

    "Bobby" <com> wrote in message news:phx.gbl... 


    Andrew Guest

Similar Threads

  1. SPROC Question, what am I doing wrong?
    By dj shane in forum Coldfusion Database Access
    Replies: 2
    Last Post: January 25th, 10:31 PM
  2. sproc in DW asp.net
    By Laura K in forum Dreamweaver AppDev
    Replies: 0
    Last Post: May 12th, 09:10 PM
  3. sproc output
    By Hassan in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 4th, 09:30 AM
  4. SPROC parameters
    By RobGT in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 24th, 03:38 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139