CASE problems with select statement.

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...
-fdAre 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;
"forest demon" <mete.hanap@.gmail.com> wrote in message
news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
> 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|||On Mar 6, 10:23=A0pm, "Mike C#" <x...@.xyz.com> wrote:
> 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 =3D '2001-08-01',
> =A0 @.end =3D '2001-08-15',
> =A0 @.dateType =3D 'Ship';
> SELECT *
> FROM Sales.SalesOrderHeader
> WHERE CASE @.dateType WHEN 'Order' THEN OrderDate
> =A0 WHEN 'Due' THEN DueDate
> =A0 WHEN 'Ship' THEN ShipDate
> =A0 END BETWEEN @.start AND @.end;
> "forest demon" <mete.ha...@.gmail.com> wrote in message
> news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
>
> > 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 =A0 <columnA> =A0 >=3D =A0 @.BeginDate =A0AND =A0 <columnA> =A0 <==3D @.EndDate
> > SELECT *
> > FROM <table>
> > WHERE =A0 <columnB> =A0 >=3D =A0 @.BeginDate =A0AND =A0 <columnB> =A0 <==3D @.EndDate
> > I know there is something simple i'm over looking.
> > thanks folks...
> > -fd- Hide quoted text -
> - 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 >=3D @.BeginDate AND S_DATE <=3D @.EndDate'
WHEN 'G_DATE' THEN 'G_DATE >=3D @.BeginDate AND G_DATE <=3D @.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...|||On Mar 6, 11:01=A0pm, forest demon <mete.ha...@.gmail.com> wrote:
> On Mar 6, 10:23=A0pm, "Mike C#" <x...@.xyz.com> wrote:
>
>
> > 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 =3D '2001-08-01',
> > =A0 @.end =3D '2001-08-15',
> > =A0 @.dateType =3D 'Ship';
> > SELECT *
> > FROM Sales.SalesOrderHeader
> > WHERE CASE @.dateType WHEN 'Order' THEN OrderDate
> > =A0 WHEN 'Due' THEN DueDate
> > =A0 WHEN 'Ship' THEN ShipDate
> > =A0 END BETWEEN @.start AND @.end;
> > "forest demon" <mete.ha...@.gmail.com> wrote in message
> >news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...=
> > > 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 =A0 <columnA> =A0 >=3D =A0 @.BeginDate =A0AND =A0 <columnA> =A0 <==3D @.EndDate
> > > SELECT *
> > > FROM <table>
> > > WHERE =A0 <columnB> =A0 >=3D =A0 @.BeginDate =A0AND =A0 <columnB> =A0 <==3D @.EndDate
> > > I know there is something simple i'm over looking.
> > > thanks folks...
> > > -fd- Hide quoted text -
> > - Show quoted text -
> sorry, i should have added a little more info. =A0i 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]
> (
> =A0 =A0 =A0 =A0 -- Add the parameters for the stored procedure here
> =A0 =A0 =A0 =A0 @.BeginDate datetime,
> =A0 =A0 =A0 =A0 @.EndDate datetime,
> =A0 =A0 =A0 =A0 @.DateType varchar(50)
> )
> AS
> BEGIN
> =A0 =A0 =A0 =A0 SET NOCOUNT ON;
> SELECT *
> FROM Permits
> WHERE CASE @.dateType
> =A0 WHEN 'S_DATE' THEN 'S_DATE >=3D @.BeginDate AND S_DATE <=3D @.EndDate'
> =A0 WHEN 'G_DATE' THEN 'G_DATE >=3D @.BeginDate AND G_DATE <=3D @.EndDate'
> END
> END
> the DateType comes in as one of two different date types. DateType
> either comes
> in as 'G_DATE' or 'S_DATE'. =A0the columns in the table are also G_DATE
> and S_DATE.
> thanks for your time...- Hide quoted text -
> - Show quoted text -
i finally figured it out...damn, i hate making silly mistakes....
thanks for your input mike...
-fd|||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
"forest demon" <mete.hanap@.gmail.com> wrote in message
news:588a87ea-7d70-45c8-83dc-ac06df09f331@.e25g2000prg.googlegroups.com...
> 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|||"Randy Pitkin" <RandyPitkin@.ydpages.com> wrote in message
news:eVT$m35gIHA.5088@.TK2MSFTNGP02.phx.gbl...
> 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.
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.sql