We help IT Professionals succeed at work.

New podcast episode! Our very own Community Manager, Rob Jurd, gives his insight on the value of an online community. Listen Now!

x

SQL Syntax

116 Views
Last Modified: 2017-04-18
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...
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

Open in new window


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)

Open in new window


any help would be appreciated
Comment
Watch Question

Lead Software Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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)
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
here:

AND (@StatusID IS NULL or (@StatusID =2 and StatusID IN (2,7,10)))

Open in new window

Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
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)

Open in new window

Author

Commented:
thanks Shaun...
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.