conditional query

Just wondering if someone could provide a brief example of how to do this. I
have a stored procedure and I need a condition where statement, for example
inputs are
@.name varchar(25)
@.color varchar(25)
if color is not 'none ' I want
select * from table1
where table1.name = @.name
and table1.color = @.color
if color is 'none' I want
select * from table1 where table1.name=@.name.
Thanks.
--
Paul G
Software engineer.One way
If @.color <> 'none'
select * from table1
where table1.name = @.name
Else
select * from table1
where table1.name = @.name
and table1.color = @.color
But there are lots of ways to to this type of processing. See
http://www.sommarskog.se/dyn-search.html
for a good discussion of ways to do this.
Tom
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:34367D41-4D61-4C01-ABC5-C5DD9DB969BD@.microsoft.com...
> Just wondering if someone could provide a brief example of how to do this.
> I
> have a stored procedure and I need a condition where statement, for
> example
> inputs are
> @.name varchar(25)
> @.color varchar(25)
> if color is not 'none ' I want
> select * from table1
> where table1.name = @.name
> and table1.color = @.color
> if color is 'none' I want
> select * from table1 where table1.name=@.name.
> Thanks.
> --
> Paul G
> Software engineer.|||create proc myProc
@.name varchar(25),
@.color varchar(25)
as
if @.color != 'none'
select * from table1
where table1.name = @.name and table1.color = @.color
if @.color = 'none'
select * from table1
where table1.name=@.name
Linchi
"Paul" wrote:
> Just wondering if someone could provide a brief example of how to do this. I
> have a stored procedure and I need a condition where statement, for example
> inputs are
> @.name varchar(25)
> @.color varchar(25)
> if color is not 'none ' I want
> select * from table1
> where table1.name = @.name
> and table1.color = @.color
> if color is 'none' I want
> select * from table1 where table1.name=@.name.
> Thanks.
> --
> Paul G
> Software engineer.|||This does it all in one query.
SELECT *
FROM table1
WHERE table1.name = @.name
AND (@.color = 'none '
OR table1.color = @.color)
Note that it MIGHT not perform as well as the alternatives using two
individual queries.
Roy Harvey
Beacon Falls, CT
On Tue, 29 Jan 2008 11:17:02 -0800, Paul
<Paul@.discussions.microsoft.com> wrote:
>Just wondering if someone could provide a brief example of how to do this. I
>have a stored procedure and I need a condition where statement, for example
>inputs are
>@.name varchar(25)
>@.color varchar(25)
>if color is not 'none ' I want
>select * from table1
>where table1.name = @.name
> and table1.color = @.color
>if color is 'none' I want
>select * from table1 where table1.name=@.name.
>Thanks.|||thanks for the responses. I simplified the example as I actually have
several items in the select statement as well as several in the where clause
as well as joins. I may have to use two seperate queries but may try to do it
with a sing query using OR if possible.
--
Paul G
Software engineer.
"Roy Harvey (SQL Server MVP)" wrote:
> This does it all in one query.
> SELECT *
> FROM table1
> WHERE table1.name = @.name
> AND (@.color = 'none '
> OR table1.color = @.color)
> Note that it MIGHT not perform as well as the alternatives using two
> individual queries.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 29 Jan 2008 11:17:02 -0800, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >Just wondering if someone could provide a brief example of how to do this. I
> >have a stored procedure and I need a condition where statement, for example
> >
> >inputs are
> >@.name varchar(25)
> >@.color varchar(25)
> >
> >if color is not 'none ' I want
> >select * from table1
> >where table1.name = @.name
> > and table1.color = @.color
> >
> >if color is 'none' I want
> >select * from table1 where table1.name=@.name.
> >Thanks.
>|||On Tue, 29 Jan 2008 12:34:23 -0800, Paul
<Paul@.discussions.microsoft.com> wrote:
>thanks for the responses. I simplified the example as I actually have
>several items in the select statement as well as several in the where clause
>as well as joins. I may have to use two seperate queries but may try to do it
>with a sing query using OR if possible.
Be aware that the warning about possible performance problems of the
all-in-one version becomes more apt as the query becomes more complex.
You may not have any problem, only trying it will determine that, but
be aware of the possibility.
Roy Harvey
Beacon Falls, CT|||ok thanks for the additional information. I have it running in the live
database, using 3 separate queries based on the condition of two input
parameters and the longest query is about 2 seconds. Fortunately the
database is relatively small and some indexes have been put into place to
enhance performance.
--
Paul G
Software engineer.
"Roy Harvey (SQL Server MVP)" wrote:
> On Tue, 29 Jan 2008 12:34:23 -0800, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >thanks for the responses. I simplified the example as I actually have
> >several items in the select statement as well as several in the where clause
> >as well as joins. I may have to use two seperate queries but may try to do it
> >with a sing query using OR if possible.
> Be aware that the warning about possible performance problems of the
> all-in-one version becomes more apt as the query becomes more complex.
> You may not have any problem, only trying it will determine that, but
> be aware of the possibility.
> Roy Harvey
> Beacon Falls, CT
>