If statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

If statement

Author

Topic

NeilC
Yak Posting Veteran

55 Posts

Posted - 2009-01-29 : 08:59:52

I am trying to add and and clause based on a value from my select statement and get Incorrect syntax near the keyword 'and'

Here is my code:



and DateAdded >= DateAdd(hh,-48, GetDate())

--filters
-- this is a university so they need to see updates from colleges and programs
If U.UserLevelID = 5
	Begin
		and  (N.NewsArea = 7  or N.NewsArea = 8)
	End

-- this is a college so they need to see updates from programs only
If U.UserLevelID = 8
	Begin
		and N.NewsArea = 8 
	End






order by DateAdded desc

Any ideas??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-29 : 09:13:07

you cant use if here. it should be


...
and DateAdded >= DateAdd(hh,-48, GetDate())
and  (((N.NewsArea = 7  or N.NewsArea = 8) and U.UserLevelID = 5)
or (U.UserLevelID = 8 and N.NewsArea = 8 ))
...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 09:16:06

Two ways

-- Way 1
AND x.DateAdded >= DATEADD(HOUR, -48, GETDATE())
AND (
	n.NewsArea IN (7, 8) AND u.UserLevelID = 5
	OR
	n.NewsArea = 8 AND u.UserLevelID = 8
    )

-- Way 2
AND x.DateAdded >= DATEADD(HOUR, -48, GETDATE())
AND (
	n.NewsArea = 8 AND u.UserLevelID IN (5, 8)
	OR
	n.NewsArea = 7 AND u.UserLevelID = 5
    )

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-29 : 09:19:47

quote:

Originally posted by Peso

Two ways

-- Way 1
AND x.DateAdded >= DATEADD(HOUR, -48, GETDATE())
AND (
	n.NewsArea IN (7, 8) AND u.UserLevelID = 5
	OR
	n.NewsArea = 8 AND u.UserLevelID = 8
    )

-- Way 2
AND x.DateAdded >= DATEADD(HOUR, -48, GETDATE())
AND (
	(n.NewsArea = 8 AND u.UserLevelID IN (5, 8))
	OR
	(n.NewsArea = 7 AND u.UserLevelID = 5)
    )