Not sure how to do this CASE statement right

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)

Not sure how to do this CASE statement right

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2009-01-02 : 11:59:10

CREATE PROCEDURE [dbo].[GetModelOrders]
@modelID int,
@PeriodList nvarchar(1)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT DISTINCT Orders.OrderID, Orders.CustomerFirstName, Orders.CustomerLastName, Orders.CustomerEmail, Orders.DateCreated
FROM dbModels_models.Orders INNER JOIN
dbModels_models.OrderDetail ON Orders.OrderID = OrderDetail.OrderID INNER JOIN
dbModels_models.pictureSet ON OrderDetail.pictureSetID = pictureSet.pictureSetID
WHERE (Orders.Verified = 1) AND (pictureSet.modelID = @modelID)

CASE WHEN @PeriodList='1' THEN AND (pictureSet.modelID = @modelID)
WHEN @PeriodList='2' THEN AND (pictureSet.modelID = @modelID)
WHEN @PeriodList='3' THEN AND (pictureSet.modelID = @modelID)
END

Select Case @PeriodList
When 0
THEN AND (pictureSet.modelID = @modelID)
When 1
THEN AND (pictureSet.modelID = @modelID)
When 2
THEN AND (pictureSet.modelID = @modelID)
End Select

END
GO

Dave
Helixpoint Web Development
http://www.helixpoint.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 12:02:39

you're evaluating the same filter for all the case branches. then wat's the purpose of case when?

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2009-01-02 : 12:06:37

I know. I did not finish it. I just get an error when I save it

Actually it is this:

CREATE PROCEDURE [dbo].[GetModelOrders]
@modelID int,
@PeriodList nvarchar(1)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT DISTINCT Orders.OrderID, Orders.CustomerFirstName, Orders.CustomerLastName, Orders.CustomerEmail, Orders.DateCreated
FROM dbModels_models.Orders INNER JOIN
dbModels_models.OrderDetail ON Orders.OrderID = OrderDetail.OrderID INNER JOIN
dbModels_models.pictureSet ON OrderDetail.pictureSetID = pictureSet.pictureSetID
WHERE (Orders.Verified = 1)

CASE WHEN @PeriodList='1' THEN AND (pictureSet.modelID = @modelID)
WHEN @PeriodList='2' THEN AND (pictureSet.modelID = @modelID)
WHEN @PeriodList='3' THEN AND (pictureSet.modelID = @modelID)
END

END
GO

Dave
Helixpoint Web Development
http://www.helixpoint.com

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-02 : 12:08:37

what are you trying to do here?

CASE has to be a part of SELECT statement.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-02 : 12:18:30

its still not very clear, do you want the where condition to change based on input parameter?

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2009-01-02 : 12:29:08

Yes I do

Dave
Helixpoint Web Development
http://www.helixpoint.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 12:32:39

quote:
Originally posted by helixpoint

Yes I do

Dave
Helixpoint Web Development
http://www.helixpoint.com

it should be like


CREATE PROCEDURE [dbo].[GetModelOrders] 
@modelID int,
@PeriodList nvarchar(1)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT DISTINCT Orders.OrderID, Orders.CustomerFirstName, Orders.CustomerLastName, Orders.CustomerEmail, Orders.DateCreated
FROM dbModels_models.Orders INNER JOIN
dbModels_models.OrderDetail ON Orders.OrderID = OrderDetail.OrderID INNER JOIN
dbModels_models.pictureSet ON OrderDetail.pictureSetID = pictureSet.pictureSetID
WHERE (Orders.Verified = 1) 
AND pictureSet.modelID =
 Case @PeriodList
When 0 
THEN @modelID
When 1 
THEN @modelID
When 2 
THEN @modelID
End 


END
GO

but i still cant understand why you're using same filter condition for all branches
(pictureSet.modelID =@modelID)

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-02 : 12:38:45

WHERE (Orders.Verified = 1)
AND pictureSet.modelID = (CASE @PeriodList WHEN '1' THEN @modelID WHEN '2' THEN @modelID WHEN '3' THEN @modelID ELSE pictureSet.modelID END)

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-02 : 12:42:55

quote:
Originally posted by visakh16
but i still cant understand why you're using same filter condition for all branches
(pictureSet.modelID =@modelID)

when @PeriodList is in 1, 2 or 3
then he wants an additional WHERE

pictureSet.modelID = @modelID

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2009-01-02 : 12:48:58

The AND values are going to be different

if @PeriodList = 1 the AND will be ---- AND (Orders.DateCreated > CONVERT(DATETIME, '2008-11-24 00:00:00', 102))
if @PeriodList = 2 the AND will be ---- AND (Orders.DateCreated > CONVERT(DATETIME, '2008-11-24 00:00:00', 102)) AND (Orders.DateCreated < CONVERT(DATETIME, '2008-11-25 00:00:00', 102))

And so on. I am trying to stay away from dynamic SQL

Dave
Helixpoint Web Development
http://www.helixpoint.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 12:58:23

quote:
Originally posted by rohitkumar

quote:
Originally posted by visakh16
but i still cant understand why you're using same filter condition for all branches
(pictureSet.modelID =@modelID)

when @PeriodList is in 1, 2 or 3
then he wants an additional WHERE

pictureSet.modelID = @modelID

i understood that. you missed my point. what i was telling was if he wants the same filter for all cases then why use case statement at all?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 12:59:18

quote:
Originally posted by helixpoint

The AND values are going to be different

if @PeriodList = 1 the AND will be ---- AND (Orders.DateCreated > CONVERT(DATETIME, '2008-11-24 00:00:00', 102))
if @PeriodList = 2 the AND will be ---- AND (Orders.DateCreated > CONVERT(DATETIME, '2008-11-24 00:00:00', 102)) AND (Orders.DateCreated < CONVERT(DATETIME, '2008-11-25 00:00:00', 102))

And so on. I am trying to stay away from dynamic SQL

Dave
Helixpoint Web Development
http://www.helixpoint.com

but still condition to filter is same isnt it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources