CASE problems with select statement.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

forest demon - 07 Mar 2008 05:12 GMT

I'm trying to build a select statement using the CASE expression. All
I want is to build the WHERE piece of the select statement based on a
parameter value.

I want to somehow CASE the WHERE clause based on a parameter of let's
say, @DateType

SELECT *
FROM <table>
WHERE <columnA> >= @BeginDate AND <columnA> <= @EndDate

SELECT *
FROM <table>
WHERE <columnB> >= @BeginDate AND <columnB> <= @EndDate

I know there is something simple i'm over looking.

thanks folks...

-fd

Reply to this Message

Mike C# - 07 Mar 2008 05:23 GMT

Are you talking about something like this? Note this sample works in the
AdventureWorks sample database:

DECLARE @start DATETIME;
DECLARE @end DATETIME;
DECLARE @dateType VARCHAR(5);

SELECT @start = '2001-08-01',
@end = '2001-08-15',
@dateType = 'Ship';

SELECT *
FROM Sales.SalesOrderHeader
WHERE CASE @dateType WHEN 'Order' THEN OrderDate
WHEN 'Due' THEN DueDate
WHEN 'Ship' THEN ShipDate
END BETWEEN @start AND @end;

> I'm trying to build a select statement using the CASE expression. All
> I want is to build the WHERE piece of the select statement based on a
[quoted text clipped - 16 lines]
>
> -fd

Reply to this Message

forest demon - 07 Mar 2008 06:01 GMT

> Are you talking about something like this? Note this sample works in the
> AdventureWorks sample database:
[quoted text clipped - 36 lines]
>
> - Show quoted text -

sorry, i should have added a little more info. i use this in a stored
procedure and
i have the following thus far.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spDataReaderStoredProcedure]
(
    -- Add the parameters for the stored procedure here
    @BeginDate datetime,
    @EndDate datetime,
    @DateType varchar(50)
)
AS
BEGIN

    SET NOCOUNT ON;

SELECT *
FROM Permits
WHERE CASE @dateType
WHEN 'S_DATE' THEN 'S_DATE >= @BeginDate AND S_DATE <= @EndDate'
WHEN 'G_DATE' THEN 'G_DATE >= @BeginDate AND G_DATE <= @EndDate'
END

END

the DateType comes in as one of two different date types. DateType
either comes
in as 'G_DATE' or 'S_DATE'. the columns in the table are also G_DATE
and S_DATE.

thanks for your time...

Reply to this Message

forest demon - 07 Mar 2008 07:48 GMT

> > Are you talking about something like this? Note this sample works in the
> > AdventureWorks sample database:
[quoted text clipped - 78 lines]
>
> - Show quoted text -

i finally figured it out....damn, i hate making silly mistakes.....

thanks for your input mike....

-fd

Reply to this Message

Randy Pitkin - 11 Mar 2008 18:38 GMT

There is case, But there is also Between

Select * from table where DateColumn Between @BeginDate and @enddate

Case should not be used in a where.

Better to create a Table Variable and load from an index of Quailifing
Primary keys.
Then Join on the Table variable.

!0 fold performance increase.

-R

> I'm trying to build a select statement using the CASE expression. All
> I want is to build the WHERE piece of the select statement based on a
[quoted text clipped - 16 lines]
>
> -fd

Reply to this Message

Mike C# - 12 Mar 2008 02:49 GMT

> There is case, But there is also Between
>
[quoted text clipped - 7 lines]
>
> !0 fold performance increase.

BETWEEN replaces the >= AND <= but doesn't do anything for the OP's question
of dynamically determining which column to use to limit rows. Whether or
not he needs a performance increase probably depends on how much data he's
querying, etc.

Reply to this Message