CASE problems with select statement.  
Author Message
rudy1178





PostPosted: Thu Mar 06 23:23:00 CST 2008 Top

SQL Server >> 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


SELECT *
FROM <table>


SELECT *
FROM <table>


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

thanks folks...

-fd

SQL Server116  
 
 
Mike





PostPosted: Thu Mar 06 23:23:00 CST 2008 Top

SQL Server >> CASE problems with select statement. Are you talking about something like this? Note this sample works in the
AdventureWorks sample database:






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

SELECT *
FROM Sales.SalesOrderHeader

WHEN 'Due' THEN DueDate
WHEN 'Ship' THEN ShipDate




> 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

>
> SELECT *
> FROM <table>

>
> SELECT *
> FROM <table>

>
> I know there is something simple i'm over looking.
>
> thanks folks...
>
> -fd


 
 
forest





PostPosted: Fri Mar 07 00:01:56 CST 2008 Top

SQL Server >> CASE problems with select statement.
> Are you talking about something like this? Note this sample works in the
> AdventureWorks sample database:
>



>



>
> SELECT *
> FROM Sales.SalesOrderHeader

> =A0 WHEN 'Due' THEN DueDate
> =A0 WHEN 'Ship' THEN ShipDate

>

>

>
>
>
> > 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

>
> > SELECT *
> > FROM <table>


>
> > SELECT *
> > FROM <table>


>
> > 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



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...

 
 
forest





PostPosted: Fri Mar 07 01:48:17 CST 2008 Top

SQL Server >> CASE problems with select statement.

>
>
>
>
>
> > Are you talking about something like this? Note this sample works in the=

> > AdventureWorks sample database:
>



>



>
> > SELECT *
> > FROM Sales.SalesOrderHeader

> > =A0 WHEN 'Due' THEN DueDate
> > =A0 WHEN 'Ship' THEN ShipDate

>

>


>
> > > 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

>
> > > SELECT *
> > > FROM <table>


>
> > > SELECT *
> > > FROM <table>


>
> > > 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



> )
> AS
> BEGIN
>
> =A0 =A0 =A0 =A0 SET NOCOUNT ON;
>
> SELECT *
> FROM Permits



> 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


 
 
Randy





PostPosted: Tue Mar 11 12:38:46 CDT 2008 Top

SQL Server >> CASE problems with select statement. There is case, But there is also Between



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
> parameter value.
>
> I want to somehow CASE the WHERE clause based on a parameter of let's

>
> SELECT *
> FROM <table>

>
> SELECT *
> FROM <table>

>
> I know there is something simple i'm over looking.
>
> thanks folks...
>
> -fd


 
 
Mike





PostPosted: Tue Mar 11 20:49:52 CDT 2008 Top

SQL Server >> CASE problems with select statement.

> There is case, But there is also Between
>

>
> 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.