-1

I want the where statement for this query to be optional depending on what the value of parameter @RetrieveAll is. If @RetrieveAll is false/null the where statement is used, if it is true it should be ignored.

    @IncludeErrors bit = 1,
    @IncludeAccess bit = 1,
    @IncludeLogins bit = 1,
    @RetrieveAll bit = NULL
SELECT
 //...
FROM
(

) AS a
WHERE
a.RowNumber BETWEEN @ItemCountStart AND @ItemCountEnd

Is there way to do this?

OrbitalFriendshipCannon
  • 157
  • 2
  • 10
  • possible duplicate of [Optional Arguments in WHERE Clause](http://stackoverflow.com/questions/10185638/optional-arguments-in-where-clause) – Tab Alleman Jul 21 '15 at 12:34

2 Answers2

0
SELECT
 //...
FROM
(

) AS a
WHERE
(a.RowNumber BETWEEN @ItemCountStart AND @ItemCountEnd AND @RetrieveAll IS NULL)
OR
@RetrieveAll IS NOT NULL
Madhivanan
  • 13,470
  • 1
  • 24
  • 29
0

You should try this,

Select * from Tablename a
Where 
1 = case when isnull(@RetriveAll,0) == 0 then 
        case when a.RowNumber BETWEEN @ItemCountStart AND @ItemCountEnd then 1 else 0 end
    else 1 end
Emipro Technologies Pvt. Ltd.
  • 14,598
  • 5
  • 45
  • 75