CTE's and IF Statements
-
I am trying to learn how to use CTE's. Unless I am mistaken it seems as if the statement following the CTE is an If statement, the CTE won't work. For instance, the following completely fictious example won't even compile:
CREATE PROCEDURE dbo.cte_test( @CustomerNumber Int)
AS
BEGIN
With MyCTe
As
(Select *
From Customers
Where CustomerNumber = @CustomerNumber)
if (@CustomerNumber = 100)
Begin
Select * from Customers
End
END
Yet if I comment out the If code or the CTE, it does compile. Can anyone please explain why this happens. I am asking because I would like to create a stored procedure that first defines a CTE and later in the code (after a few if statements) uses that CTE. Can this be done?
Thanks
-
try like the following:
CREATE PROCEDURE dbo.cte_test( @CustomerNumber Int)
AS
BEGIN
if (@CustomerNumber = 100 or @CustomerNumber > 0)
Begin
With MyCTe
As
(Select *
From Customers
Where CustomerNumber = @CustomerNumber)
Select * from Customers
End
END
-
Kishore.P (11/10/2008)
try like the following:CREATE PROCEDURE dbo.cte_test( @CustomerNumber Int)
AS
BEGIN
if (@CustomerNumber = 100 or @CustomerNumber > 0)
Begin
With MyCTe
As
(Select *
From Customers
Where CustomerNumber = @CustomerNumber)
Select * from Customers
End
END
Although this code will be compiled, it seems to have a mistake. You are defining the CTE and then performing the select statement on Customers table.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
I'm not sure CTEs are the best solution here. Perhaps using a temp table or a table variable to hold the row or rows you require. Make your select at the top of the procedure, and then use the temp table throughout the rest of the procedure.
Tom
Life: it twists and turns like a twisty turny thing
-
meichner (11/10/2008)
I am trying to learn how to use CTE's. Unless I am mistaken it seems as if the statement following the CTE is an If statement, the CTE won't work. For instance, the following completely fictious example won't even compile:CREATE PROCEDURE dbo.cte_test( @CustomerNumber Int)
AS
BEGIN
With MyCTe
As
(Select *
From Customers
Where CustomerNumber = @CustomerNumber)
if (@CustomerNumber = 100)
Begin
Select * from Customers
End
END
Yet if I comment out the If code or the CTE, it does compile. Can anyone please explain why this happens. I am asking because I would like to create a stored procedure that first defines a CTE and later in the code (after a few if statements) uses that CTE. Can this be done?
Thanks
Not in the way you are expecting it to...from BOL:
Guidelines for Creating and Using CTEsThe following guidelines apply to nonrecursive CTEs. For guidelines that apply to recursive CTEs,
see "Guidelines for Defining and Using Recursive CTEs" that follows.
A CTE must be followed by a SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns.
A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
meichner (11/10/2008)
I am trying to learn how to use CTE's. Unless I am mistaken it seems as if the statement following the CTE is an If statement, the CTE won't work. For instance, the following completely fictious example won't even compile:CREATE PROCEDURE dbo.cte_test( @CustomerNumber Int)
AS
BEGIN
With MyCTe
As
(Select *
From Customers
Where CustomerNumber = @CustomerNumber)
if (@CustomerNumber = 100)
Begin
Select * from Customers
End
END
Yet if I comment out the If code or the CTE, it does compile. Can anyone please explain why this happens. I am asking because I would like to create a stored procedure that first defines a CTE and later in the code (after a few if statements) uses that CTE. Can this be done?
Thanks
See if this works
CREATE PROCEDURE dbo.cte_test( @CustomerNumber Int)AS
BEGIN
With MyCTe
As
(Select *
From Customers
Where CustomerNumber = @CustomerNumber or @CustomerNumber = 100)
Select * from MyCTe
END
END
MadhivananFailing to plan is Planning to fail
-
Not in the way you are expecting it to...from BOL:
Guidelines for Creating and Using CTEsThe following guidelines apply to nonrecursive CTEs. For guidelines that apply to recursive CTEs,
see "Guidelines for Defining and Using Recursive CTEs" that follows.
A CTE must be followed by a SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns.
A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.
Everyone seems to have taken my fictious example too literally. I was'nt trying to solve that specific problem. I was trying to convey the problem in
a general manner. Obviously I missed the mark. I wanted to know why I couldn't follow a CTE with an If Statement.
Chris's response answered the question (thanks Chris). I somehow missed the section from the BOL that Chris posted (I guess I need more coffee).
Does anyone know why the CTE works this way?
It seems that by forcing the following statement to be a Select, Insert, Update or Delete the use of the CTE is limited beyond what it could be used for.
Thanks again to everyone - sorry if I was confusing.
-
CTEs can be used to replace derived tables, temporary tables or table variables. They tend to be more readable than using nested subqueries. In fact, they're very good if you're referencing the same subquery multiple times because you can define it once and reference it many times.
Also, recursive CTEs are good for querying hierarchal data.
Greg
-
G² (11/10/2008)
CTEs can be used to replace derived tables, temporary tables or table variables. They tend to be more readable than using nested subqueries. In fact, they're very good if you're referencing the same subquery multiple times because you can define it once and reference it many times.Also, recursive CTEs are good for querying hierarchal data.
Greg
That is the reason that I started to look into using CTE's. The issue that I have is that I can create a temporary table in the beginning of a stored procedure and then use that temporary table anywhere in the procedure whereas a CTE must be followed by a Select, Insert, Update or Delete.
For instance, if my stored procedure followed the following steps:
Step #1 - Create Temporary Table
Step #2 - If @SomeParameter is Null Then Do Something, Otherwise Do Something Else
won't work if Step #1 Creates a CTE instead of a Temporary table.
That is why I stated that the CTE seems to be limited. I guess that is a limitation that I will have to accept in order to get the other benefits of the CTE.
Thanks
-
You're right. CTEs are limited in that they are meant to be a one-time view that gets used immediately and then destroyed. To do what you intend, you may have to do the IF check first and then have two different CTEs for each outcome. In this case, you're probably better off using a temp table or a table variable.
-
CTE's aren't temporary tables. They are much more related to derived tables.
-
G² (11/10/2008)
You're right. CTEs are limited in that they are meant to be a one-time view that gets used immediately and then destroyed. To do what you intend, you may have to do the IF check first and then have two different CTEs for each outcome. In this case, you're probably better off using a temp table or a table variable.Thanks, everyone has been very helpful.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply