conditional WHERE clause

Hi
I have an SP with a few params and I wish to use one of the params in the
WHERE clause, to conditionaly set a WHERE statement, for example:
CREATE PROC sp_Test
@.ProductCode VARCHAR(5)
@.ProcessMonth INT = NULL,
@.ProcessYear INT = NULL
AS
SELECT Table.ID
FROM Table
WHERE
Table.PMonth = @.ProcessMonth
AND Table.PYear = @.ProcessYear
AND
IF @.ProductCode = 'ALL' THEN
BEGIN
Table.ProductCode = ('A','B','C' etc...all codes, need this to
be dynamic)
END
ELSE
BEGIN
Table.ProductCode = @.ProductCode
END
Hope this makes sense, I also don't know how to make the 'ALL' return all
records?
Kind Regards
RickyPWHERE
ProductCode = CASE @.ProductCode WHEN 'ALL' THEN ProductCode ELSE
@.ProductCode END
"ricky" <ricky@.ricky.com> wrote in message
news:efjM0q$UGHA.5248@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have an SP with a few params and I wish to use one of the params in the
> WHERE clause, to conditionaly set a WHERE statement, for example:
> CREATE PROC sp_Test
> @.ProductCode VARCHAR(5)
> @.ProcessMonth INT = NULL,
> @.ProcessYear INT = NULL
> AS
> SELECT Table.ID
> FROM Table
> WHERE
> Table.PMonth = @.ProcessMonth
> AND Table.PYear = @.ProcessYear
> AND
> IF @.ProductCode = 'ALL' THEN
> BEGIN
> Table.ProductCode = ('A','B','C' etc...all codes, need this
> to
> be dynamic)
> END
> ELSE
> BEGIN
> Table.ProductCode = @.ProductCode
> END
> Hope this makes sense, I also don't know how to make the 'ALL' return all
> records?
> Kind Regards
> RickyP
>|||You may benefit by reading the article at:
http://www.sommarskog.se/dyn-search.html
Anith|||Hi
use northwind
go
create proc spproc
@.custid varchar(10)
as
select * from orders where CustomerID=
case when @.custid='all' then CustomerID else @.custid end
--usage
exec spproc 'vinet'
exec spproc 'all'
"ricky" <ricky@.ricky.com> wrote in message
news:efjM0q$UGHA.5248@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have an SP with a few params and I wish to use one of the params in the
> WHERE clause, to conditionaly set a WHERE statement, for example:
> CREATE PROC sp_Test
> @.ProductCode VARCHAR(5)
> @.ProcessMonth INT = NULL,
> @.ProcessYear INT = NULL
> AS
> SELECT Table.ID
> FROM Table
> WHERE
> Table.PMonth = @.ProcessMonth
> AND Table.PYear = @.ProcessYear
> AND
> IF @.ProductCode = 'ALL' THEN
> BEGIN
> Table.ProductCode = ('A','B','C' etc...all codes, need this
> to
> be dynamic)
> END
> ELSE
> BEGIN
> Table.ProductCode = @.ProductCode
> END
> Hope this makes sense, I also don't know how to make the 'ALL' return all
> records?
> Kind Regards
> RickyP
>|||Hi Ricky,
How about :
SELECT * FROM MyTable
WHERE (Table.ProductCode = @.ProductCode OR @.ProductCode = 'ALL')
Should do what you're looking for. Personally, rather than 'ALL' I'd use
NULL to indicate that no filter should be applied (i.e. all product codes),
but this will work fine.
Cheers,
Alex
"ricky" <ricky@.ricky.com> wrote in message
news:efjM0q$UGHA.5248@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have an SP with a few params and I wish to use one of the params in the
> WHERE clause, to conditionaly set a WHERE statement, for example:
> CREATE PROC sp_Test
> @.ProductCode VARCHAR(5)
> @.ProcessMonth INT = NULL,
> @.ProcessYear INT = NULL
> AS
> SELECT Table.ID
> FROM Table
> WHERE
> Table.PMonth = @.ProcessMonth
> AND Table.PYear = @.ProcessYear
> AND
> IF @.ProductCode = 'ALL' THEN
> BEGIN
> Table.ProductCode = ('A','B','C' etc...all codes, need this
> to
> be dynamic)
> END
> ELSE
> BEGIN
> Table.ProductCode = @.ProductCode
> END
> Hope this makes sense, I also don't know how to make the 'ALL' return all
> records?
> Kind Regards
> RickyP
>|||Great minds think alike - thanks guys for the postings, didn't know you
could do that.
Kind Regards
RickyP
"ricky" <ricky@.ricky.com> wrote in message
news:efjM0q$UGHA.5248@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have an SP with a few params and I wish to use one of the params in the
> WHERE clause, to conditionaly set a WHERE statement, for example:
> CREATE PROC sp_Test
> @.ProductCode VARCHAR(5)
> @.ProcessMonth INT = NULL,
> @.ProcessYear INT = NULL
> AS
> SELECT Table.ID
> FROM Table
> WHERE
> Table.PMonth = @.ProcessMonth
> AND Table.PYear = @.ProcessYear
> AND
> IF @.ProductCode = 'ALL' THEN
> BEGIN
> Table.ProductCode = ('A','B','C' etc...all codes, need this
to
> be dynamic)
> END
> ELSE
> BEGIN
> Table.ProductCode = @.ProductCode
> END
> Hope this makes sense, I also don't know how to make the 'ALL' return all
> records?
> Kind Regards
> RickyP
>