none
select statement with different where clause

    Question

  • Hi,

    We are using sql server 2005. I need help to build this.

    My stored proc has few parameters like this:

    Create procedure uspGetList @pincludeall bit = 0, @pIncludeCash bit = 0, @pincludeTempList bit = 0

    AS

    BEGIN

              select 'ALL' as category, 0 as order where @pincludeall = 1

              union

              select 'Group' as category, 1 as order where @pIncludeCash = 1

              union

              select category, 2 as order from table1 where cashcategory = 1

    END

    Now, I need to use the same stored proc but using a different field for table query like this in addition to above but replace where condition. So, I thought of passing another parameter for new one..

    Create procedure uspGetList @pincludeall bit = 0, @pIncludeCash bit = 0, @pincludeTempList bit = 0

    AS

    BEGIN

              select 'ALL' as category, 0 as order where @pincludeall = 1

              union

              select 'Group' as category, 1 as order where @pIncludeCash = 1

              union

              select category, 2 as order from table1 where

                                     case when @pincludeTempList = 1 then  isTempList = 1

                                     else   cashcategory = 1 end

    END

    So, what I want is if @pIncludeTempList = 1, then use that field in where clause else other field. So, I want select query with 1 field in where cluase depending on parameter value equal to 1.

    Thanks

    Tuesday, October 29, 2013 6:24 PM

Answers

  • Create procedure uspGetList @pincludeall bit = 0, @pIncludeCash bit = 0, @pincludeTempList bit = 0
    
    AS
    
    BEGIN
    
              select 'ALL' as category, 0 as order where @pincludeall = 1
    
              union
    
              select 'Group' as category, 1 as order where @pIncludeCash = 1
    
              union
    
              select category, 2 as order from table1 
      where (@pincludeTempList = 1 and isTempList = 1)
      or (@pincludeTempList = 0 and cashcategory = 1)
    
    END


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by Spunny Tuesday, October 29, 2013 6:39 PM
    Tuesday, October 29, 2013 6:31 PM

All replies

  • Create procedure uspGetList @pincludeall bit = 0, @pIncludeCash bit = 0, @pincludeTempList bit = 0
    
    AS
    
    BEGIN
    
              select 'ALL' as category, 0 as order where @pincludeall = 1
    
              union
    
              select 'Group' as category, 1 as order where @pIncludeCash = 1
    
              union
    
              select category, 2 as order from table1 
      where (@pincludeTempList = 1 and isTempList = 1)
      or (@pincludeTempList = 0 and cashcategory = 1)
    
    END


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by Spunny Tuesday, October 29, 2013 6:39 PM
    Tuesday, October 29, 2013 6:31 PM
  • There is a very good article on this type of dynamic search at http://www.sommarskog.se/dyn-search.html.  When you get there you will find two links.  Since you are on SQL 2005, you will want the 2005 link.

    BTW, if you are doing unions and you know there are no duplicates in the rows that will be returned use UNION ALL, not UNION.  UNION ALL is more efficient.  UNION eliminates duplicates, when means it has to sort the result to look for and eliminate any duplicate rows.  If you know you can't have duplicates, UNION ALL can avoid doing that sort.

    Tom

    Tuesday, October 29, 2013 6:37 PM
  • Thank you Russel. It worked.
    Tuesday, October 29, 2013 6:40 PM
  • Thanks Tom. I will take a look at it and change to Union ALL.
    Tuesday, October 29, 2013 6:41 PM