jknj72
asked on
SQL Syntax
I have a Where Clause that works fine but now I need to check for status 2 and get statusid 2,7 and 10 but im having a little trouble
It was...
and now it is...
any help would be appreciated
It was...
Where
(@UserID IS NULL OR wwt.UserID = @UserID)
AND (@StatusID IS NULL OR StatusID = @StatusID)
AND (@StartDay IS NULL OR StartDay >= @StartDay)
AND (@EndDay IS NULL OR EndDay <= @EndDay)
Order By WWT.WeekID desc
and now it is...
Where
(@UserID IS NULL OR wwt.UserID = @UserID)
AND CASE WHEN @StatusID = 2 THEN StatusID IN(2,7,10) ELSE (@StatusID IS NULL OR StatusID = @StatusID) END
AND (@StartDay IS NULL OR StartDay >= @StartDay)
AND (@EndDay IS NULL OR EndDay <= @EndDay)
any help would be appreciated
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Explain at a Barney Level (i.e. 5 year old level) what exactly you are trying to do with the 2, 7, 10, as you can't have an expression where a value is set to an IN range of three other values.
Please try
Where
(@UserID IS NULL OR wwt.UserID = @UserID)
AND (
(@StatusID IS NULL OR StatusID = @StatusID)
OR
(@StatusID = 2 AND StatusID IN(2,7,10) )
)
AND (@StartDay IS NULL OR StartDay >= @StartDay)
AND (@EndDay IS NULL OR EndDay <= @EndDay)
Where
(@UserID IS NULL OR wwt.UserID = @UserID)
AND (
(@StatusID IS NULL OR StatusID = @StatusID)
OR
(@StatusID = 2 AND StatusID IN(2,7,10) )
)
AND (@StartDay IS NULL OR StartDay >= @StartDay)
AND (@EndDay IS NULL OR EndDay <= @EndDay)
here:
AND (@StatusID IS NULL or (@StatusID =2 and StatusID IN (2,7,10)))
WHERE
wwt.UserID = isnull(@UserID, wwt.UserID)
AND 1 = CASE WHEN @StatusID = 2 THEN case when StatusID IN (2, 7, 10) then 1 else 0 end ELSE case when isnull(@StatusID, StatusID) = StatusID then 1 else 0 END end
AND StartDay >= isnull(@StartDay, StartDay)
AND EndDay <= isnull(@EndDay, EndDay)
ASKER
thanks Shaun...