CASE with empty parameter

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Not Me
    #1

    CASE with empty parameter

    Hi, I can't seem to get this where clause to function as I wish.. must be
    something simple but I can't see it!

    have tried
    ac.coursename like CASE @course WHEN null THEN '%' ELSE '%' + @course + '%'
    ac.coursename like CASE @course WHEN '' THEN '%' ELSE '%' + @course + '%'
    ac.coursename like CASE len(@course) WHEN 0 THEN '%' ELSE '%' + @course +
    '%'

    All work fine when I enter something in the parameter, but not when I leave
    it blank! Any ideas why this is the ...case?

    Cheers!
    Chris


    Tags: None
    • Not Me
      #2
      Re: CASE with empty parameter

      "Not Me" <Not.Me@faker.f ake.fa.kee> wrote in message
      news:c974cs$e7l $1@ucsnew1.ncl. ac.uk...[color=blue]
      > Hi, I can't seem to get this where clause to function as I wish.. must be
      > something simple but I can't see it!
      >
      > have tried
      > ac.coursename like CASE @course WHEN null THEN '%' ELSE '%' + @course +[/color]
      '%'[color=blue]
      > ac.coursename like CASE @course WHEN '' THEN '%' ELSE '%' + @course + '%'
      > ac.coursename like CASE len(@course) WHEN 0 THEN '%' ELSE '%' + @course +
      > '%'
      >
      > All work fine when I enter something in the parameter, but not when I[/color]
      leave[color=blue]
      > it blank! Any ideas why this is the ...case?[/color]

      Ok try #4, I did
      ac.coursename like CASE WHEN @course is null THEN '%' ELSE '%' + @course +
      '%'

      And it works! Don't you just hate discovering the answer yourself straight
      after asking the question? :op

      Chris


        Comment

        • Not Me
          #3
          Re: CASE with empty parameter

          "Not Me" <Not.Me@faker.f ake.fa.kee> wrote in message
          news:c975lv$et0 $1@ucsnew1.ncl. ac.uk...[color=blue]
          > "Not Me" <Not.Me@faker.f ake.fa.kee> wrote in message
          > news:c974cs$e7l $1@ucsnew1.ncl. ac.uk...[/color]

          New problem :)

          Along the same lines as above, i.e. wanting to include all rows if no
          criteria specified - What can I do in the case of integers? what would be
          the %/catch-all for this?
          Is there a better way alltogether of using 'optional' parameters?

          Any help much appreciated!
          Chris




            Comment

            • tperovic
              #4
              Re: CASE with empty parameter

              I've been using this:

              ac.coursename = COALESCE(@cours e,ac.coursename )

              When @course is null then the column is compared to itself which is always
              true.


              "Not Me" <Not.Me@faker.f ake.fa.kee> wrote in message
              news:c974cs$e7l $1@ucsnew1.ncl. ac.uk...[color=blue]
              > Hi, I can't seem to get this where clause to function as I wish.. must be
              > something simple but I can't see it!
              >
              > have tried
              > ac.coursename like CASE @course WHEN null THEN '%' ELSE '%' + @course +[/color]
              '%'[color=blue]
              > ac.coursename like CASE @course WHEN '' THEN '%' ELSE '%' + @course + '%'
              > ac.coursename like CASE len(@course) WHEN 0 THEN '%' ELSE '%' + @course +
              > '%'
              >
              > All work fine when I enter something in the parameter, but not when I[/color]
              leave[color=blue]
              > it blank! Any ideas why this is the ...case?
              >
              > Cheers!
              > Chris
              >
              >[/color]


                Comment

                • Not Me
                  #5
                  Re: CASE with empty parameter

                  "tperovic" <tonyperovic@ya hoo.com> wrote in message
                  news:pnHtc.2861 8$zO3.1966@news read2.news.atl. earthlink.net.. .[color=blue]
                  > "Not Me" <Not.Me@faker.f ake.fa.kee> wrote in message
                  > news:c974cs$e7l $1@ucsnew1.ncl. ac.uk...[color=green]
                  > > ac.coursename like CASE @course WHEN null THEN '%' ELSE '%' + @course +[/color]
                  > '%'[color=green]
                  > > ac.coursename like CASE @course WHEN '' THEN '%' ELSE '%' + @course +[/color][/color]
                  '%'[color=blue][color=green]
                  > > ac.coursename like CASE len(@course) WHEN 0 THEN '%' ELSE '%' + @course[/color][/color]
                  +[color=blue][color=green]
                  > > '%'
                  > >
                  > > All work fine when I enter something in the parameter, but not when I[/color]
                  > leave it blank![/color]
                  [color=blue]
                  > I've been using this:
                  >
                  > ac.coursename = COALESCE(@cours e,ac.coursename )
                  >
                  > When @course is null then the column is compared to itself which is always
                  > true.[/color]

                  Aha, so much better! Thanks for that.

                  Chris


                    Comment

                    • Erland Sommarskog
                      #6
                      Re: CASE with empty parameter

                      Not Me (Not.Me@faker.f ake.fa.kee) writes:[color=blue]
                      > Along the same lines as above, i.e. wanting to include all rows if no
                      > criteria specified - What can I do in the case of integers? what would be
                      > the %/catch-all for this?[/color]

                      Depends on your business domain, but NULL would certainly be the
                      best choice.
                      [color=blue]
                      > Is there a better way alltogether of using 'optional' parameters?[/color]

                      Don't really know what you are into, but
                      http://www.sommarskog.se/dyn-search.html might be something for you.


                      --
                      Erland Sommarskog, SQL Server MVP, sommar@algonet. se

                      Books Online for SQL Server SP3 at
                      Microsoft SQL Server
                      http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
                      Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

                        Comment

                        • Not Me
                          #7
                          Re: CASE with empty parameter


                          "Erland Sommarskog" <sommar@algonet .se> wrote in message
                          news:Xns94F929C 429C8Yazorman@1 27.0.0.1...[color=blue]
                          > Not Me (Not.Me@faker.f ake.fa.kee) writes:[color=green]
                          > > Along the same lines as above, i.e. wanting to include all rows if no
                          > > criteria specified - What can I do in the case of integers? what would[/color][/color]
                          be[color=blue][color=green]
                          > > the %/catch-all for this?[/color]
                          >
                          > Depends on your business domain, but NULL would certainly be the
                          > best choice.
                          >[color=green]
                          > > Is there a better way alltogether of using 'optional' parameters?[/color]
                          >
                          > Don't really know what you are into, but
                          > http://www.sommarskog.se/dyn-search.html might be something for you.[/color]

                          Thanks for that!

                          Chris


                            Comment

                            Previous template Next
                            Working...