Conditional/Dynamic Where Clause

Hi all,
I want to construct a dynamic where clause depending on the
value of the parameter of the stored procedure.
Here' s a snippet of the Stored procedure that I want to write
Create Procedure mySP
@.FilterBy --declare parameter
As
Select * from Registration
where
--This is where i need help.
The values for @.FilterBy can be only either A or B or C or D.
If value of @.FilterBy is A then I would like the where clause to be :
Where Registration.A = 'Something'
If value of @.FilterBy is B then I would like the where clause to be :
Where Registration.B = 'Something'
If value of @.FilterBy is C then I would like the where clause to be :
Where Registration.C = 'Something'
If value of @.FilterBy is D then I would like the where clause to be :
Where Registration.D = 'Something'
Is this possible? If yes, how? I will greatly appreciate any help.
TIA,
Mounil.
Give this a try. It should be what you are looking for I think.
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
)
AS
SET NOCOUNT ON
DECLARE @.SqlDynvarchar(4000)
SELECT @.SqlDyn = 'SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.A = "Something"'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B = "Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C = "Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D = "Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Mounilk" wrote:

> Hi all,
> I want to construct a dynamic where clause depending on the
> value of the parameter of the stored procedure.
> Here' s a snippet of the Stored procedure that I want to write
> Create Procedure mySP
> @.FilterBy --declare parameter
> As
> Select * from Registration
> where
> --This is where i need help.
>
> The values for @.FilterBy can be only either A or B or C or D.
> If value of @.FilterBy is A then I would like the where clause to be :
> Where Registration.A = 'Something'
> If value of @.FilterBy is B then I would like the where clause to be :
> Where Registration.B = 'Something'
> If value of @.FilterBy is C then I would like the where clause to be :
> Where Registration.C = 'Something'
> If value of @.FilterBy is D then I would like the where clause to be :
> Where Registration.D = 'Something'
> Is this possible? If yes, how? I will greatly appreciate any help.
> TIA,
> Mounil.
>
|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn?
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn?
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.
|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn?
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn?
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.