IF condition with CTE - 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 2008 Forums

Transact-SQL (2008)

IF condition with CTE

Author

Topic

ssunny
Posting Yak Master

133 Posts

Posted - 2012-08-15 : 16:46:22

Hello,

Back again with a CTE question.My code looks like this.

declare @a int
set @a= 1

with test
as
(
select cid,cdate from c
)

if @a = 1
select * from test order by cid
else
select * from test order by cid desc

When I try to execute above query, I get following error:

Incorrect syntax near the keyword 'if'.

Thanks in advace.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-15 : 16:58:02

I don't think SQL will let you use a conditional execution construct (IF) after a cte like that. You might try a dynamic order by:

SELECT * 
FROM Test  
ORDER BY 
    CASE WHEN @a = 1 THEN cid ELSE NULL END
    ,CASE WHEN @a <> 1 THEN cid ELSE NULL END DESC

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-15 : 17:03:09

Another option is to use a ranking function:

declare @a int
set @a= 1

with test
as
(
    select 
        cid,
        cdate,
        ROW_NUMBER() OVER (ORDER BY cid ASC) AS CidAsc,
        ROW_NUMBER() OVER (ORDER BY cid DESC) AS CidDesc, 
    from c
)
SELECT *
FROM Test
ORDER BY
    CASE 
        WHEN @a = 1 then CidAsc
        ELSE CidDesc
    END

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-15 : 17:04:50

Did a qick search and there are some more simplified versions of what I did above:
http://stackoverflow.com/questions/3659981/sql-server-dynamic-order-by-with-mixed-datatypes-is-this-a-good-idea

ssunny
Posting Yak Master

133 Posts

Posted - 2012-08-15 : 17:10:02

Thanks Lamprey. I will try other solutions and will update you.

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2012-08-15 : 17:14:30

declare @a int
set @a = 1

;with test as
( select cid, cdate from c )
select * from test order by
CASE WHEN @a = 1 THEN cid ELSE '1' END ASC,
CASE WHEN @a <> 1 THEN cid ELSE '1' END DESC

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2012-08-15 : 17:17:33

lol, oops, about the same as Lamprey's, guess i should refresh before posting :0

ssunny
Posting Yak Master

133 Posts

Posted - 2012-08-15 : 17:23:42

After looking closely at the code I found out that my actual criteria is this:

if @a = 1
select * from test order by cid
else
select * from test where cid < = @a order by cid

Sorry about that.

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2012-08-15 : 17:36:54

declare @a int
set @a = 1

;with test as
( select cid, cdate from c )
select * from test
where (@a = 1) OR (cid <= @a)
order by cid

ssunny
Posting Yak Master

133 Posts

Posted - 2012-08-15 : 17:50:33

Super. Works great. Thank you samuelclay and Lamprey. You guys rock:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources