multiple CTE's with control flow

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

deepthi.duvvuru@gmail.com - 31 Jul 2008 16:04 GMT

Hi all -

I'm new to Sql server and i'm stuck with an issue with CTE.

I have a situation where i have to use the following logic...

If (Condition)
begin
;with MyCTE as()
select * from MyCTE
end
else if (Condition)
begin
;with MyCTE1 as ()
select * from MyCTE1
end

I'm having problem with this type of code.
Is this valid or possible at all with CTE's.

Please advise.

Thanks

Reply to this Message

Tom Moreau - 31 Jul 2008 16:09 GMT

Is it possible to fuse the condition into the CTE's such that you eliminate
the IF - ELSE?

Signature

Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

Reply to this Message

deepthi.duvvuru@gmail.com - 31 Jul 2008 16:16 GMT

> Is it possible to fuse the condition into the CTE's such that you eliminate
> the IF - ELSE?
[quoted text clipped - 33 lines]
>
> Thanks

Based on the If condition the where clause of the select statement in
CTE changes. So i had to go for this logic.

Reply to this Message

Tom Moreau - 31 Jul 2008 16:30 GMT

WHERE clauses can get creative. Without seeing your DDL and actual specs,
we won't be able to determine a solution for you.

Signature

Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

On Jul 31, 11:09 am, "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote:

> Is it possible to fuse the condition into the CTE's such that you
> eliminate
[quoted text clipped - 34 lines]
>
> Thanks

Based on the If condition the where clause of the select statement in
CTE changes. So i had to go for this logic.

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 31 Jul 2008 16:30 GMT

Based on the If condition the where clause of the select statement in
CTE changes. So i had to go for this logic.

Can you be more specific? A lot of times, an IF condition can be changed to
a CASE expression and included in a WHERE clause.

Reply to this Message

Alex Kuznetsov - 31 Jul 2008 17:00 GMT

On Jul 31, 10:04 am, deepthi.duvv...@gmail.com wrote:

> Hi all -
>
[quoted text clipped - 15 lines]
> I'm having problem with this type of code.
> Is this valid or possible at all with CTE's.

can you wrap your selects as stored procedures? Your code might be
simple, as follows:

f (Condition)
begin
EXEC proc1
end
else if (Condition)
begin
EXEC proc2
end

Reply to this Message

deepthi.duvvuru@gmail.com - 31 Jul 2008 17:38 GMT

> On Jul 31, 10:04 am, deepthi.duvv...@gmail.com wrote:
>
[quoted text clipped - 31 lines]
>
> - Show quoted text -

The logic is like ...
If (@var = 0 or @var = 1)
begin

;with myCTE (a,b,c) as
(select * from a inner join b on a.col = b.col
left join c on a.col = c.col
where a.col1 in (@INPar1,INPar2) and
b.Col2 between isnull(@Date1,Datecol) and isnull(@Date2,Datecol))

select * from D inner join E
where col in (select * from myCTE)

end

else if (@var = 2)
begin

;with myCTE (a,b,c) as
(select * from a inner join b on a.col = b.col
where a.col1 = @INPar1)

select * from D inner join E
where col in (select * from myCTE)

end

I also can try using two stored procedures as per Alex's suggestion,
but want to know if the above logic is valid at all or not.

Thanks all.

Reply to this Message

Hugo Kornelis - 31 Jul 2008 18:04 GMT

>I also can try using two stored procedures as per Alex's suggestion,
>but want to know if the above logic is valid at all or not.

Hi deepthi.duvvuru,

The logic is valid. (Though I prefer having the semicolon at the end of
each statement instead of right in front of some statements that happen
to need it)

What problems were you having with the code? The only problem I see in
your code is using a JOIN but no ON condition.

Signature

Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Reply to this Message

deepthi.duvvuru@gmail.com - 31 Jul 2008 18:13 GMT

On Jul 31, 1:04 pm, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALID> wrote:

> On Thu, 31 Jul 2008 09:38:54 -0700 (PDT), deepthi.duvv...@gmail.com
> wrote:
[quoted text clipped - 14 lines]
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

I was geeting a syntax error at IF...
Never mind i got it working now by properly placing the begin and end
statements

Thanks all for youe help.

Reply to this Message