Prev: Bad column prefix, why does it work? (2000)
Next: SSIS Variables and variables. What is best practise.
From: tshad on 23 Mar 2010 12:43
I am trying to use the same SP to use Common Table Expressions with a
different anchor (yes, Celko anchor).

I think I can do this with a where clause but is there a way to do this with
a Case statement?

Just curious.
With a Where Clause I might do something like:

CREATE PROCEDURE GetGroups
@UserID int = NULL,
@GroupID int = NULL
AS

With MyCTE
AS
(
SELECT ParentID=0,ChildID=(a)UserID,Level = 0
WHERE (@GroupID IS NOT NULL)
UNION
SELECT ParentID = 0, ChildID = GroupID, Level=0
WHERE (@UserID IS NOT NULL or UserID = @UserID)
UNION ALL
SELECT ...
)
SELECT ...

How would I change this using a Case statement?

Normally a Case statement would be inside the Select statement but in this
case it would be outside the select statement but inside the CTE.

Thanks,

Tom


From: Plamen Ratchev on 23 Mar 2010 12:53
CASE is an expression and not a statement and you cannot use it to control flow. You can use CASE expression or other
logic in the WHERE clause if you need to pull different result set, or simply use IF based on some condition and run two
different CTE queries.

--
Plamen Ratchev
http://www.SQLStudio.com
From: Michael MacGregor on 23 Mar 2010 14:09
Perhaps it would help if you were to provide a more detailed example of what
exactly you are trying to achieve, i.e. DDL for any tables involved, example
data for the tables in the form of INSERTs, example parameter data, and
example resultset(s).

Michael MacGregor


 | 
Pages: 1
Prev: Bad column prefix, why does it work? (2000)
Next: SSIS Variables and variables. What is best practise.