Conditonal WHERE clause

Hi,

I have a sproc, called spGetJobs, which is querying a table called Jobs. Jobs are either filled or not filled. If filled, the DateJobFilled field will have a date value. If not filled, that field is null. The sproc takes a parameter to indicate either take all jobs or only unfilled jobs. I tried to solve this with a CASE statement in the WHERE clause, as in the following:

ALTER PROCEDURE dbo.spGetJobs
(
@.UnfilledJobs bit, -- if 1, get only unfilled jobs, else all jobs
@.StartDate smalldatetime
)
AS

select j.JobID, c.ClientID, j.JobStart, j.JobEnd
from Jobs j
join Clients c on j.ClientID = c.ClientID
where j.JobStart >= @.StartDate
and j.Role = 'client'
and (case when @.UnfilledJobs = 1 then j.JobFilledDate is not null

else 1 = 1 end)

However, VS complains of a syntax error when I try to save this.

I suppose I could construct the SELECT statement as a string and then execute it, but would rather not have to do that. Any suggestions as how to make a conditional where clause?

Thanks.

I think the problem is when your AND clause here:

Code Snippet

and (case when @.UnfilledJobs = 1 then j.JobFilledDate is not null

else 1 = 1 end)

What are you trying to accomplish with this clause? Maybe you need something like this?

Code Snippet

and ( @.UnfilledJobs = 1 and j.JobFilledDate is null or
@.unfilledJobs = 0
)

or maybe:

Code Snippet

and ( j.JobFilledDate is null or @.unfilledJobs = 0 )

|||

For Better performance use the if .. else statement; You can avoid the table scan,

Code Snippet

ALTER PROCEDURE dbo.spGetJobs

(

@.UnfilledJobs bit, -- if 1, get only unfilled jobs, else all jobs

@.StartDate smalldatetime

)

AS

If @.UnfilledJobs = 1

select j.JobID, c.ClientID, j.JobStart, j.JobEnd

from Jobs j

join Clients c on j.ClientID = c.ClientID

where j.JobStart >= @.StartDate

and j.Role = 'client'

and j.JobFilledDate is not null

else

select j.JobID, c.ClientID, j.JobStart, j.JobEnd

from Jobs j

join Clients c on j.ClientID = c.ClientID

where j.JobStart >= @.StartDate

and j.Role = 'client'

|||

Thanks. Your second code snippet did the trick. I had previously considered the if..else construct suggested by the next message, but the query is actually much more complex than what I posted (I stripped out all the unnecessary joins to simplify the issue) and I really don't want to repeat the entire query. Also, I don't think performance will be a significant issue here.

conditions in where clause

Hi I am upsizing the access database to SQL 2005.
I currently converting Access Query's to SQL stored procs and functions but i have got stuck on one issue.

In access there is a update statement that has an IIF in the where clause, i have tried replicating this in T-SQL buy using case statement but it does not work.

in access the where looks some thing like this Where IIF(table1.Column1 = 0,Table2.column1,Table3.Column1)

Any one know how i can best replicate this behavior?

Dagz

In place of IIF, use CASE. Using your example:

Where IIF(table1.Column1 = 0,Table2.column1,Table3.Column1)

Code Snippet

WHERE CASE Table1.Column1
WHEN 0 THEN Table2.Column1
ELSE Table3.Column1
END

|||Here you go

Code Snippet

Case WHEN table1.Column1 = 0 THEN Table2.Column1
else Table3.Column1
END|||

when i try this i get the error message, where is of none boolean type?

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.

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 @.SqlDyn varchar(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.

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 @.SqlDyn varchar(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.sqlsql

conditional WHERE sections.

Hi I have a query where I am reading in a bunch of veriables and only want to
run parts of the where clause if the variables are valid, (will be input in a
stored procedure)
I tried
WHERE
if @.var1 IS NULL
BEGIN
{
tablename.fieldname >= @.var1
}
END
if @.var2 IS NULL
BEGIN
{
AND tablename.fieldname >= @.var2
}
END
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation occures
thanks.
Paul G
Software engineer.
Paul
Create a dynamic SQL statement and build the where clause. Then execute the
SQL statement:
declare @.sql varchar(8000)
set @.sql = 'select * from table where '
If @.var1 is null
set @.sql = @.sql + 'condition 1'
else
set @.sql = @.sql + 'condition 2'
exec @.sql
"Paul" wrote:

> Hi I have a query where I am reading in a bunch of veriables and only want to
> run parts of the where clause if the variables are valid, (will be input in a
> stored procedure)
> I tried
> WHERE
> if @.var1 IS NULL
> BEGIN
> {
> tablename.fieldname >= @.var1
> }
> END
> if @.var2 IS NULL
> BEGIN
> {
> AND tablename.fieldname >= @.var2
> }
> END
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation occures
> thanks.
>
> --
> Paul G
> Software engineer.
|||There are several approaches for handling such requirements. Some of the
popular ones are detailed at: http://www.sommarskog.se/dyn-search.html
Anith
|||Hi thanks for the information. Figured there may be several ways to the
solution.
"Anith Sen" wrote:

> There are several approaches for handling such requirements. Some of the
> popular ones are detailed at: http://www.sommarskog.se/dyn-search.html
> --
> Anith
>
>
|||Ok looks like the dynamic SQL statement should work for what I am trying to
do. Thanks.
"Bruce" wrote:
[vbcol=seagreen]
> Paul
> Create a dynamic SQL statement and build the where clause. Then execute the
> SQL statement:
> declare @.sql varchar(8000)
> set @.sql = 'select * from table where '
> If @.var1 is null
> set @.sql = @.sql + 'condition 1'
> else
> set @.sql = @.sql + 'condition 2'
> exec @.sql
>
> "Paul" wrote:

Conditional where clause, depending on parameter

I am looking for a way to create a query that, depending on the value of a
parameter, builds the correct where-clause. This should be usable in a store
d
procedure.
Example:
parameter @.ShowArchived
select x, y, z from table_zyx WHERE ...
if @.ShowArchived > 0 --> WHERE archive=1
else --> WHERE archive=0 OR archive is null
All help is more than welcome!Hmm perhaps something like this:
WHERE isnull(archive,0) = case when @.ShowArchived > 0 then 1 else 0 end
it isn't optimal but you can change it if it works for you.
MC
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||http://www.sommarskog.se/dyn-search.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||you can use dynamic sql.
potentially a "simpler" to understand solution, and sometimes faster to
run is to have different select statements separted by if statements
stuffed into a stored procedure.

Conditional where clause, depending on parameter

I am looking for a way to create a query that, depending on the value of a
parameter, builds the correct where-clause. This should be usable in a stored
procedure.
Example:
parameter @.ShowArchived
select x, y, z from table_zyx WHERE ...
if @.ShowArchived > 0 --> WHERE archive=1
else --> WHERE archive=0 OR archive is null
All help is more than welcome!Hmm perhaps something like this:
WHERE isnull(archive,0) = case when @.ShowArchived > 0 then 1 else 0 end
it isn't optimal but you can change it if it works for you.
MC
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||http://www.sommarskog.se/dyn-search.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||you can use dynamic sql.
potentially a "simpler" to understand solution, and sometimes faster to
run is to have different select statements separted by if statements
stuffed into a stored procedure.

Conditional Where Clause w/ Case Statement Possible?

Greetings,

After many hours search many forums and many failed experiments, I figure it's time to turn to the experts.

I need to execute a query that changes the returned data based upon a parameter's value. In my example below, the lob field contains both text values and nulls.

SELECT uniqueID, lob, xdate

FROM mytable

WHERE

CASE WHEN @.myparam = 'ALL'

THEN

xdate >= '2007-09-01'

ELSE

xdate >= '2007-09-01' or

lob = @.myparm

END

I've experimented with various forms of the LIKE function, checking for null/not null and keep coming up blank.

I thought about using an IF statement and creating different versions of the entire statement, however, in real-life I need to do this with four fields using four parameters (one for each field). The permutations are a little too much.

Any ideas?

Rob

Your query can be written this way, I think:

SELECT uniqueID, lob, xdate

FROM mytable

WHERE (xdate >= '20070901')

AND (

@.myparm = 'ALL'

OR

lob = @.myparm

)

In general, you can probably write:

WHERE

(@.p = 'Option1' AND (<option 1 condition>))

OR

(@.p = 'Option 2' AND (<option 2 condition>))

OR

...

Be careful where parentheses go, so AND and OR don't associate differently than you want.

In SQL, CASE .. END is an expression, by the way, so it can't be used as you hoped in your post.

Steve Kass

Drew University

http://www.stevekass.com

|||

This is really bad idea to write the logical expression to validate the variable’s value on the where clause, it might force the engine to use the index scan,

If .. Else is not harm to use in your query.. Don’t try to reduce the no of lines, check the performance..

Code Snippet

If @.myparam = 'ALL'

SELECT

uniqueID, lob, xdate

FROM

mytable

WHERE

xdate >= '2007-09-01'

ELSE

SELECT

uniqueID, lob, xdate

FROM

mytable

WHERE

xdate >= '2007-09-01' or lob = @.myparm

|||

Steve Kass wrote:

Your query can be written this way, I think:

SELECT uniqueID, lob, xdate

FROM mytable

WHERE (xdate >= '20070901')

AND (

@.myparm = 'ALL'

OR

lob = @.myparm

)

In SQL, CASE .. END is an expression, by the way, so it can't be used as you hoped in your post.

Steve - when @.myparm ='ALL', I want all of the records returned. The field (lob) does not actually contain the value 'ALL'.

I tried using a LIKE statement but found that nulls are not returned. For example, if @.myparm was populated with "%" and used in this query:

SELECT uniqueID, lob, xdate

FROM mytable

WHERE (xdate >= '20070901') and

lob LIKE @.myparm

then all records with a value in lob are returned but those will a null are not. I need to inlcude the records with a null.

Rob

|||

Manivannan.D.Sekaran wrote:

This is really bad idea to write the logical expression to validate the variable’s value on the where clause, it might force the engine to use the index scan,

If .. Else is not harm to use in your query.. Don’t try to reduce the no of lines, check the performance..

The problem with using the IF statement is the number of permutation when I have four variables.

IF @.myparm1 = 'ALL' and @.myparm2 <> 'ALL' and @.myparm3 <> 'ALL' and @.myparm4 <> 'ALL'

(query 1)

IF @.myparm1 <> 'ALL' and @.myparm2 = 'ALL' and @.myparm3 <> 'ALL' and @.myparm4 <> 'ALL'

(query 2)

IF @.myparm1 <> 'ALL' and @.myparm2 <> 'ALL' and @.myparm3 = 'ALL' and @.myparm4 <> 'ALL'

(query 3)

...

I think I'd wind up with 16 IF statements. While it should work, it would be very difficult to support.

Rob

|||

In your case the best solution is using the dynamic sql... Buliding the Where clause expression on the fly..

There are few limitations there while using the dynamic sql.. If you really need a best performance there is no compromise..

example,

Code Snippet

Declare @.SQL as Varchar(8000);

Declare @.Name as Varchar(8000);

Declare @.Type as Varchar(8000);

Declare @.Where as varchar(8000);

Set @.Name = null;

Set @.Type ='U'

Set @.SQL = 'Select * from Sysobjects Where 1=1'

Set @.Where = ''

IF @.Name is not NULL

Set @.Where = ' And Name=''' + @.Name + ''''

IF @.Type is NOT NULL

Set @.Where = @.Where + ' And Type=''' + @.Type + ''''

Exec (@.SQL + @.Where)

Code Snippet

Declare @.SQL as NVarchar(4000);

Declare @.Name as Varchar(8000);

Declare @.Type as Varchar(8000);

Declare @.Where as varchar(8000);

Set @.Name = NULL;

Set @.Type ='P'

Set @.SQL = 'Select * from Sysobjects Where 1=1'

Set @.Where = ''

IF @.Name is not NULL

Set @.Where = ' And Name=@.Name'

IF @.Type is NOT NULL

Set @.Where = @.Where + ' And Type=@.Type'

Set @.SQL = @.SQL + @.Where

Exec sp_executesql @.SQL, N'@.name varchar(100), @.type varchar(100)', @.name, @.type

|||Sorry, Rob. I misread your original intent, so let me try again. I think you want

1. If 'ALL' is passed: every row with xdate >= '20070901'
2. If 'ALL' is not passed: every row with xdate >= '20070901', as well as rows with lob = @.myparm, regardless of date.

First, a solution that assumes @.myparm is never NULL:

select uniqueID, lob, xdate
from mytable
where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
)

Because you use NULL as a value for [lob], and you use @.myparm = NULL to select those rows, you can't rely on @.myparm = lob, which is not true when @.myparm and lob are both NULL. Unfortunately, you have to handle this separately, because T-SQL has no IS NOT DISTINCT FROM operator that means "are both equal or are both null". Whether you treat it as a third case or in the second case is up to you:

where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
) or (
@.myparm IS NULL
and
(lob IS NULL or xdate >= '20070901')
)

So this is like what a CASE statement would be. The structure is

where
you are in case 1 and the where clause for that case holds
or
you are in case 2 and the where clause for that case holds
...

The problem with NULL is because a CASE expression would allow an OTHERWISE clause to handle both @.myparm <> 'ALL' and @.myparm IS NULL at once.

Note that I wrote the date without hyphens. Unfortunately, if xdate is [datetime] or [smalldatetime], SQL Server installations that use European and many other non-US localizations will interpret the date as you wrote it to mean January 9, 2007. I doubt you ever want that, and you can avoid a surprise by using the format I provided, or the other "safe" format '2007-09-01T00:00:00' (the T is required).

SK

|||

Steve Kass wrote:

select uniqueID, lob, xdate
from mytable
where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
)

SK

Steve,

Thank you very much. This did the trick. It's amazing what a few AND & OR statements can do with the correct paranthesis.

I'm actually using these queres in SSRS as a data source. It took a couple of edits to get SSRS to take the paranthesis correctly as it want to "fix" them for you.

Rob

Conditional Where Clause w/ Case Statement Possible?

Greetings,

After many hours search many forums and many failed experiments, I figure it's time to turn to the experts.

I need to execute a query that changes the returned data based upon a parameter's value. In my example below, the lob field contains both text values and nulls.

SELECT uniqueID, lob, xdate

FROM mytable

WHERE

CASE WHEN @.myparam = 'ALL'

THEN

xdate >= '2007-09-01'

ELSE

xdate >= '2007-09-01' or

lob = @.myparm

END

I've experimented with various forms of the LIKE function, checking for null/not null and keep coming up blank.

I thought about using an IF statement and creating different versions of the entire statement, however, in real-life I need to do this with four fields using four parameters (one for each field). The permutations are a little too much.

Any ideas?

Rob

Your query can be written this way, I think:

SELECT uniqueID, lob, xdate

FROM mytable

WHERE (xdate >= '20070901')

AND (

@.myparm = 'ALL'

OR

lob = @.myparm

)

In general, you can probably write:

WHERE

(@.p = 'Option1' AND (<option 1 condition>))

OR

(@.p = 'Option 2' AND (<option 2 condition>))

OR

...

Be careful where parentheses go, so AND and OR don't associate differently than you want.

In SQL, CASE .. END is an expression, by the way, so it can't be used as you hoped in your post.

Steve Kass

Drew University

http://www.stevekass.com

|||

This is really bad idea to write the logical expression to validate the variable’s value on the where clause, it might force the engine to use the index scan,

If .. Else is not harm to use in your query.. Don’t try to reduce the no of lines, check the performance..

Code Snippet

If @.myparam = 'ALL'

SELECT

uniqueID, lob, xdate

FROM

mytable

WHERE

xdate >= '2007-09-01'

ELSE

SELECT

uniqueID, lob, xdate

FROM

mytable

WHERE

xdate >= '2007-09-01' or lob = @.myparm

|||

Steve Kass wrote:

Your query can be written this way, I think:

SELECT uniqueID, lob, xdate

FROM mytable

WHERE (xdate >= '20070901')

AND (

@.myparm = 'ALL'

OR

lob = @.myparm

)

In SQL, CASE .. END is an expression, by the way, so it can't be used as you hoped in your post.

Steve - when @.myparm ='ALL', I want all of the records returned. The field (lob) does not actually contain the value 'ALL'.

I tried using a LIKE statement but found that nulls are not returned. For example, if @.myparm was populated with "%" and used in this query:

SELECT uniqueID, lob, xdate

FROM mytable

WHERE (xdate >= '20070901') and

lob LIKE @.myparm

then all records with a value in lob are returned but those will a null are not. I need to inlcude the records with a null.

Rob

|||

Manivannan.D.Sekaran wrote:

This is really bad idea to write the logical expression to validate the variable’s value on the where clause, it might force the engine to use the index scan,

If .. Else is not harm to use in your query.. Don’t try to reduce the no of lines, check the performance..

The problem with using the IF statement is the number of permutation when I have four variables.

IF @.myparm1 = 'ALL' and @.myparm2 <> 'ALL' and @.myparm3 <> 'ALL' and @.myparm4 <> 'ALL'

(query 1)

IF @.myparm1 <> 'ALL' and @.myparm2 = 'ALL' and @.myparm3 <> 'ALL' and @.myparm4 <> 'ALL'

(query 2)

IF @.myparm1 <> 'ALL' and @.myparm2 <> 'ALL' and @.myparm3 = 'ALL' and @.myparm4 <> 'ALL'

(query 3)

...

I think I'd wind up with 16 IF statements. While it should work, it would be very difficult to support.

Rob

|||

In your case the best solution is using the dynamic sql... Buliding the Where clause expression on the fly..

There are few limitations there while using the dynamic sql.. If you really need a best performance there is no compromise..

example,

Code Snippet

Declare @.SQL as Varchar(8000);

Declare @.Name as Varchar(8000);

Declare @.Type as Varchar(8000);

Declare @.Where as varchar(8000);

Set @.Name = null;

Set @.Type ='U'

Set @.SQL = 'Select * from Sysobjects Where 1=1'

Set @.Where = ''

IF @.Name is not NULL

Set @.Where = ' And Name=''' + @.Name + ''''

IF @.Type is NOT NULL

Set @.Where = @.Where + ' And Type=''' + @.Type + ''''

Exec (@.SQL + @.Where)

Code Snippet

Declare @.SQL as NVarchar(4000);

Declare @.Name as Varchar(8000);

Declare @.Type as Varchar(8000);

Declare @.Where as varchar(8000);

Set @.Name = NULL;

Set @.Type ='P'

Set @.SQL = 'Select * from Sysobjects Where 1=1'

Set @.Where = ''

IF @.Name is not NULL

Set @.Where = ' And Name=@.Name'

IF @.Type is NOT NULL

Set @.Where = @.Where + ' And Type=@.Type'

Set @.SQL = @.SQL + @.Where

Exec sp_executesql @.SQL, N'@.name varchar(100), @.type varchar(100)', @.name, @.type

|||Sorry, Rob. I misread your original intent, so let me try again. I think you want

1. If 'ALL' is passed: every row with xdate >= '20070901'
2. If 'ALL' is not passed: every row with xdate >= '20070901', as well as rows with lob = @.myparm, regardless of date.

First, a solution that assumes @.myparm is never NULL:

select uniqueID, lob, xdate
from mytable
where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
)

Because you use NULL as a value for [lob], and you use @.myparm = NULL to select those rows, you can't rely on @.myparm = lob, which is not true when @.myparm and lob are both NULL. Unfortunately, you have to handle this separately, because T-SQL has no IS NOT DISTINCT FROM operator that means "are both equal or are both null". Whether you treat it as a third case or in the second case is up to you:

where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
) or (
@.myparm IS NULL
and
(lob IS NULL or xdate >= '20070901')
)

So this is like what a CASE statement would be. The structure is

where
you are in case 1 and the where clause for that case holds
or
you are in case 2 and the where clause for that case holds
...

The problem with NULL is because a CASE expression would allow an OTHERWISE clause to handle both @.myparm <> 'ALL' and @.myparm IS NULL at once.

Note that I wrote the date without hyphens. Unfortunately, if xdate is [datetime] or [smalldatetime], SQL Server installations that use European and many other non-US localizations will interpret the date as you wrote it to mean January 9, 2007. I doubt you ever want that, and you can avoid a surprise by using the format I provided, or the other "safe" format '2007-09-01T00:00:00' (the T is required).

SK

|||

Steve Kass wrote:

select uniqueID, lob, xdate
from mytable
where
(
@.myparm = 'ALL'
and
(xdate >= '20070901')
) or (
@.myparm <> 'ALL'
and
(@.myparm = lob or xdate >= '20070901')
)

SK

Steve,

Thank you very much. This did the trick. It's amazing what a few AND & OR statements can do with the correct paranthesis.

I'm actually using these queres in SSRS as a data source. It took a couple of edits to get SSRS to take the paranthesis correctly as it want to "fix" them for you.

Rob

|||in t-sql you can have conditional where clauses
sample:

select * from mytable
where
mycol = case when @.i = 1 then 1 else 2 end

Conditional Where clause possible?

Is it possible to use a conditional statements in a where clause?

IE: I have 3 paramaters that may or may not be filled.

I would like to do something along the lines of...

Select * From (tables)

WHERE

If @.param1 has value

Begin

'run this where statement

if @.Param2 has value

'add this to the where clause

if @.param3 has value

'add this to the where cluase

Dynamic Search Conditions in T-SQL

http://www.sommarskog.se/dyn-search.html

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB

|||

thanks but I can't get to those websites...

Our company websense filters that out as "personal"

|||

Sometimes you can get away with something like:

Select * From (tables)

WHERE

(Field1 = @.param1 OR @.param1 IS NULL) AND

(Field 2 = @.param2 OR @.param2 IS NULL) AND ...

|||

Then tell your IT department that they are actually work related and why and ask them to allow access to them.

Simple as that.

|||

Mainiac007,

You can't do conditional code in T-SQL (unlike PL/SQL). You can, however, do this:

Select*From(tables)

where

col1 =coalesce(@.param1, col1)

and col2 =coalesce(@.param2, col2)

and...

Ron

Conditional WHERE clause

Hi,

[SQL 2005 Express]

I would like a DropDownList to be populated differently depending on the selected value in a FormView.

If the FormView's selected value (CompanyID) is 2, then the DropDownList should show all Advisers from the relevant Company. Otherwise, the DropDownList should show all Advisers from the relevant Company where the TypeID field is 3.

Here is the SQL for case 1:

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
(CompanyID = @.CompanyID).

Here's the SQL for case 2:

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
(CompanyID = @.CompanyID) AND
(TypeID = 3).

Here's my best (failed) attempt to get what I want:

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
IF @.CompanyID = 2 THEN
BEGIN
(CompanyID = @.CompanyID)
END
ELSE
BEGIN
(CompanyID = @.CompanyID) AND
(TypeID = 3)
END

I've also tried:

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
CASE @.CompanyID
WHEN 2 THEN (CompanyID = @.CompanyID)
ELSE (CompanyID = @.CompanyID) AND
(TypeID = 3)
END

and

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
CASE WHEN (@.CompanyID = 2) THEN (CompanyID = @.CompanyID)
ELSE (CompanyID = @.CompanyID) AND (TypeID = 3)
END

I'd be very grateul to know (a) what the correct syntax for this is and (b) if it can be achieved using a parametised query, rather than a stored procedure.

Thanks very much.

Regards

Gary

Gary,

I think you are trying to construct a select based on the values selected in some control in your web form. (correct me if i am wrong) while going through your issue, i think the following solution should work -

Generate a dynamic where clause -

declare @.SQLSelect varchar(4000),

@.SQLWhere varchar(2000)

set @.SQLSelect = 'SELECT
AdviserID,
AdviserName
FROM
Advisers '

If @.CompanyID = 2

Begin

@.SQLWhere = ' Where CompanyID = ' + @.CompanyID

End

Else

Begin

@.SQLWhere = ' Where CompanyID = ' + @.CompanyID + ' and TypeID = 3'
End

set @.SQLSelect = @.SQLSelect + @.SQLWhere

Execute @.SQLSelect

I think this will help you to think further, if didn't solve your problem.

Ash

|||

Thanks, Ash.

I suspect that I'm too much of a novice to know what to do with what you've posted.

I've tried executing it as a Query and as a Stored Procedure - with no success. I ended up simplifying it to the same WHERE clause, with no better results:

Here is the simplified query:

DECLARE @.SQLSelect varchar(4000), @.SQLWhere varchar(2000)
SET @.SQLSelect = 'SELECT AdviserID, AdviserName FROM Advisers '
IF @.AdviserCompanyID = 2
BEGIN
@.SQLWhere = ' Where AdviserCompanyID = ' + @.AdviserCompanyID
END
ELSE
BEGIN
@.SQLWhere = ' Where AdviserCompanyID = ' + @.AdviserCompanyID
END
SET @.SQLSelect = @.SQLSelect + @.SQLWhere
EXECUTE @.SQLSelect

This generated: "Must Declare the scalar variable @.AdviserCompany"

So, I changed the first line to:

DECLARE @.SQLSelect varchar(4000), @.SQLWhere varchar(2000), @.AdviserCompanyID int

This generated:"Incorrect syntax near '@.SQLWhere'"

No joy after much fiddling. I then tried to create a Stored Procedure - with similar success levels.

What am I not getting?

Thanks very much for your help.

regards

Gary

|||

Gary:

You are not the only one. It took me hours to come to this solution.

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:mytestConnectionString %>"SelectCommand="SELECT [AdviserID], [AdviserName] FROM [Adviser]

WHERE CompanyID = CASE @.CompanyID WHEN 2 THEN 2 ELSE @.CompanyID END AND

TypeID = CASE @.CompanyID WHEN 2 THEN TypeID else 3 END"

><SelectParameters><asp:ControlParameterControlID="FormView1$companyIDtxtbox"Name="CompanyID"Type="Int32"/></SelectParameters></asp:SqlDataSource>|||

Excellent, Limno - thanks very much! Works a charm...

(Still keen to find out if I was doing something stupid to prevent the Previous idea from working...Anyone?)

Regards

Gary

|||

Hi Guys,

I would like to apply Limnon's solution to the WHERE clause in the following select statement:

SELECT
FIInvestments.InvestmentID,
Accounts.AccountName + ' - ' + CAST(Accounts.AccountNumber AS varchar(20)) + CONVERT
varchar, FIInvestments.InvestmentDate, 3) + ': ' + ' ($' + LEFT (CAST(FIInvestments.Amount AS varchar),
LEN(CAST(FIInvestments.Amount AS varchar)) - 3) + ' for ' + CAST(FIInvestments.Term AS varchar(3)) + '
months)' AS Investment
FROM
FIInvestments INNER JOIN
Accounts ON FIInvestments.AccountID = Accounts.AccountID
WHERE (FIInvestments.FundID = @.FundID) AND (NOT EXISTS
(SELECT PaymentID, CommPaymentID, Date, InvestmentID, Amount, Notes
FROM FICommPayments
WHERE (InvestmentID = FIInvestments.InvestmentID)))

(In other words: I want all the Investments with the selected FundID, unless commission has already been paid on them, as evidenced by payment records in the FICommPayments table. This is so I can allocate commissionpayments to them).

However, there is one Fund/FundID (FundID = 141) which pays commission in dribs and drabs, so if that Fund is selected, I wantall the investments within that Fund - not just the ones that haven't had commission paid against them yet.

So, here are my two WHERE statements:

WHERE (FIIinvestments.FundID = @.FundID)

and

WHERE (FIInvestments.FundID = @.FundID) AND (NOT EXISTS
(SELECT PaymentID, CommPaymentID, Date, InvestmentID, Amount, Notes
FROM FICommPayments
WHERE (InvestmentID = FIInvestments.InvestmentID)))

And here's my best attempt at a conditional WHERE statement so far:

WHERE CASE @.FundID WHEN 141 THEN (FIInvestments.FundID = @.FundID) ELSE (FIInvestments.FundID = @.FundID) AND (NOT EXISTS (SELECT PaymentID, CommPaymentID, Date, InvestmentID, Amount, Notes FROM FICommPayments WHERE (InvestmentID = FIInvestments.InvestmentID))) END

The error messages this generates are:

Incorrect syntax near '='.
Incorrect syntax near ')'.

I also tried, with little hope:

WHERE (FIInvestments.FundID = @.FundID) CASE @.FundID WHEN NOT 141 THEN (NOT EXISTS
(SELECT PaymentID, CommPaymentID, Date, InvestmentID, Amount, Notes
FROM FICommPayments
WHERE (InvestmentID = FIInvestments.InvestmentID)))END

This generated:

Incorrect syntax near the keyword 'NOT'.
Incorrect syntax near ')'

Perhaps I should revert to Ash's solution - but I got stuck on that one, too!

Thanks for the help.

Regards

Gary

|||

Hello:

I don't know why your approaches did not work. You may post that question again if you want an answer. Instead, you can split your condition in two separate parts, then use UNION OR UNION ALL (with possible duplicates) to combine the results.

I used a simplified version of your tables to test the following script, it works as to my understanding. But you may need to tweak it for your real case.

SELECT FIInvestments.InvestmentID, FIInvestments.FUNDID
FROM FIInvestments WHERE FundID<>141 AND FundID=@.FundID AND FIInvestments.FundID NOT IN (SELECT FICommPayments.FUNDID
FROM FICommPayments INNER JOIN FIInvestments ON FICommPayments.InvestmentID = FIInvestments.InvestmentID)
UNION ALL
SELECT FIInvestments.InvestmentID, FIInvestments.FUNDID
FROM FIInvestments
WHERE FundID=141 ANDFundID=@.FundID

|||

Excellent, Limon!

I started off thinking that your suggestion wasn't exactly what I'm after in this case (because I need one list for case 141 and the other for every other case, so the UNION ALL didn't seem like what I was after until it dawned on me what you're doing - very sneaky!).

I've been looking for UNION / UNION ALL for other reasons, so I get a double hit out of this one. Thanks very much - you're making my day on a number of fronts at the moment (including on the other thread)!

If this keeps up much longer, I'll have to put you on a retainer. In fact, I think that the guru's amongst you should work out an easy way of allowing novices like me to secure a commercial agreement/service with you guys in addition to this freebie one. I know that it exposes the community to abuse, but I'm sure there must be a way of doing it... I'll keep thinking and ewxperiencing and come up with something over the next month or two.

Regards

Gary

Conditional Where Clause

Hi - I am writing a C# program using SQL Server. The form I have is
collecting search criteria for a database. The main 3 fields are Category,
Type, Author.
Any combination of the 3 fields can be used. That is, All 3 fields can be
used to search on, or just 2 or just 1. If the user selects 1 or 2 fields,
I
can't use the 3rd field in the where clause of the query.
How can I create a generic query and pass a string for the "where" clause
instead of creating 7 specific queries for each possible combination of
search criteria.
Thanks,
SarahCREATE PROCEDURE getbook
@.category VARCHAR(10) = NULL,
@.bookType VARCHAR(10) = NULL,
@.author VARCHAR(10) = NULL
AS
SELECT category,
booktype,
author
FROM book
WHERE (category = @.category OR @.category IS NULL )
AND (booktype = @.bookType OR @.bookType IS NULL )
AND (author = @.author OR @.author IS NULL )|||Sarah,
From what you say is the following correct:
You want a Stored Procedure that takes 3 parameters and returns a recordset
based on the passed parameters.
The SELECT statement itself will be static, and both the first two params
will be used if present, and the third [arameter will only be used if neithe
r
of the first two params are present.
Does that sum it up?
Tony
"Sarah Sarah" wrote:

> Hi - I am writing a C# program using SQL Server. The form I have is
> collecting search criteria for a database. The main 3 fields are Category
,
> Type, Author.
> Any combination of the 3 fields can be used. That is, All 3 fields can b
e
> used to search on, or just 2 or just 1. If the user selects 1 or 2 fields
, I
> can't use the 3rd field in the where clause of the query.
> How can I create a generic query and pass a string for the "where" clause
> instead of creating 7 specific queries for each possible combination of
> search criteria.
> Thanks,
> Sarah|||Sarah,
Bearing in mind the mutual exclusivity between params 1,2 and param 3, the
following code will work:::
CREATE STORED PROCEDURE [dbo].[usp_GetSearchResults]
@.Category varchar(100)='',
@.Type varchar(100)='',
@.Author varchar(100)
AS
DECLARE @.sSQL varchar(2000)
SET @.sSQL = ''
IF @.Category ='' AND @.Type =''
BEGIN
SET @.sSQL = @.sSQL + ' SELECT Category, Type, Author '
SET @.sSQL = @.sSQL + ' FROM tblMYTABLE '
SET @.sSQL = @.sSQL + ' WHERE (@.Category='' OR Category=' + CHAR(39) +
@.Category + CHAR(39) + ') '
SET @.sSQL = @.sSQL + ' AND (@.Author ='' OR Author=' + CHAR(39) + @.Author +
CHAR(39) + ') '
END
ELSE
BEGIN
SET @.sSQL = @.sSQL + ' SELECT Category, Type, Author '
SET @.sSQL = @.sSQL + ' FROM tblMYTABLE '
SET @.sSQL = @.sSQL + ' WHERE (@.Category='' OR Category=' + CHAR(39) +
@.Category + CHAR(39) + ') '
SET @.sSQL = @.sSQL + ' AND (@.Type ='' OR Type=' + CHAR(39) + @.Type +
CHAR(39) + ') '
END
EXEC (@.sSQL)
You do not necessarily need the character string to create the select
statement, it does help with debugging though.
Hope it helps,
Tony
"Sarah Sarah" wrote:

> Hi - I am writing a C# program using SQL Server. The form I have is
> collecting search criteria for a database. The main 3 fields are Category
,
> Type, Author.
> Any combination of the 3 fields can be used. That is, All 3 fields can b
e
> used to search on, or just 2 or just 1. If the user selects 1 or 2 fields
, I
> can't use the 3rd field in the where clause of the query.
> How can I create a generic query and pass a string for the "where" clause
> instead of creating 7 specific queries for each possible combination of
> search criteria.
> Thanks,
> Sarah|||KenJ - thanks - this logic will work, but I am getting a syntax error:
"Duplicated parameter names are not allowed"
when I try to do this in Query builder. Any idea what would cause this erro
r.
Thanks,
Sarah
"KenJ" wrote:

> CREATE PROCEDURE getbook
> @.category VARCHAR(10) = NULL,
> @.bookType VARCHAR(10) = NULL,
> @.author VARCHAR(10) = NULL
> AS
> SELECT category,
> booktype,
> author
> FROM book
> WHERE (category = @.category OR @.category IS NULL )
> AND (booktype = @.bookType OR @.bookType IS NULL )
> AND (author = @.author OR @.author IS NULL )
>|||I'm not familiar with query builder. Can you run it in query analyzer?
Here is a sample script that creates a table, loads some dummy data,
runs the procedure with several variations then drops the table and
procedure. I've run it in query analyzer to be sure it works...
USE tempdb
GO
SET nocount ON
GO
CREATE TABLE book (
bookid INT IDENTITY( 1 , 1 ) NOT NULL PRIMARY KEY
, category VARCHAR(10) NULL
, booktype VARCHAR(10) NULL
, author VARCHAR(10) NULL)
GO
INSERT book
VALUES('fiction'
, 'paperback'
, 'twain')
INSERT book
VALUES('fiction'
, 'hardbound'
, 'asimov')
INSERT book
VALUES('fiction'
, 'paperback'
, 'rand')
GO
CREATE PROCEDURE getbook
@.category VARCHAR(10) = NULL
, @.bookType VARCHAR(10) = NULL
, @.author VARCHAR(10) = NULL
AS
SELECT category
, booktype
, author
FROM book
WHERE (category = @.category
OR @.category IS NULL )
AND (booktype = @.bookType
OR @.bookType IS NULL )
AND (author = @.author
OR @.author IS NULL )
GO
-- get all fiction books
EXEC getbook @.category = 'fiction'
-- all fiction books by rand
EXEC getbook @.category = 'fiction' ,
@.author = 'rand'
-- all paperbacks
EXEC getbook @.bookType = 'paperback'
-- returns all books since we don't supply any filter
EXEC getbook
GO
DROP TABLE book
GO
DROP PROCEDURE getbook
GO|||On Wed, 1 Feb 2006 16:50:27 -0800, Sarah Sarah wrote:

>Hi - I am writing a C# program using SQL Server. The form I have is
>collecting search criteria for a database. The main 3 fields are Category,
>Type, Author.
>Any combination of the 3 fields can be used. That is, All 3 fields can be
>used to search on, or just 2 or just 1. If the user selects 1 or 2 fields,
I
>can't use the 3rd field in the where clause of the query.
>How can I create a generic query and pass a string for the "where" clause
>instead of creating 7 specific queries for each possible combination of
>search criteria.
Hi Sarah,
Many ways to skin this cat can be found i Erland Sommarskog's article:
http://www.sommarskog.se/dyn-search.html
Hugo Kornelis, SQL Server MVP|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
It would also help if you would learn that a field and a column nothing
whatsoever alike and that names like "type", "category", etc. are too
vague to be data element names/. Try something like this:
CREATE PROCEDURE GetBook
(@.my_book_category VARCHAR(10) = NULL, -- wild guess
@.my_book_type INTEGER = NULL, -- Dewey Decimal ?
@.my_author_name VARCHAR(25) = NULL)
AS
SELECT book_category, book_type, author_name
FROM Library
WHERE book_category = COALESCE (@.my_book_category, book_category)
AND book_type = COALESCE (@.my_book_type, book_type)
AND author_name = COALESCE (@.my_author_name, author_)name) ;|||> WHERE book_category = COALESCE (@.my_book_category, book_category)
> AND book_type = COALESCE (@.my_book_type, book_type)
> AND author_name = COALESCE (@.my_author_name, author_)name) ;
That would give a tablescan.
Can you imagine how badly that will perform on a table with a few million
rows perhaps 1GB in size.
To do the tablescan everytime a user ran the query SQL Server would have to
read 1GB of data.
Now, multiply that by 10 users, thats 10GB of data SQL Server now needs to
read in order to process all 10 queries.
You are going to need one hell of a big box!
The correct way to do this is to either use IF..ELSE to make the query more
specific depending on which parameters are specified, ie. only put the
parameters specified on the WHERE clause.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138944895.900338.14850@.g47g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> It would also help if you would learn that a field and a column nothing
> whatsoever alike and that names like "type", "category", etc. are too
> vague to be data element names/. Try something like this:
> CREATE PROCEDURE GetBook
> (@.my_book_category VARCHAR(10) = NULL, -- wild guess
> @.my_book_type INTEGER = NULL, -- Dewey Decimal ?
> @.my_author_name VARCHAR(25) = NULL)
> AS
> SELECT book_category, book_type, author_name
> FROM Library
> WHERE book_category = COALESCE (@.my_book_category, book_category)
> AND book_type = COALESCE (@.my_book_type, book_type)
> AND author_name = COALESCE (@.my_author_name, author_)name) ;
>|||Don't forget Ken that the query below will give you a very general plan so
you'll probably end up doing a table scan.
Check the plan before you decided on the solution.
Much better to use IF ELSE or dynamic SQL and taylor your query to the
parameters passed.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"KenJ" <kenjohnson@.hotmail.com> wrote in message
news:1138899994.839100.3010@.o13g2000cwo.googlegroups.com...
> I'm not familiar with query builder. Can you run it in query analyzer?
> Here is a sample script that creates a table, loads some dummy data,
> runs the procedure with several variations then drops the table and
> procedure. I've run it in query analyzer to be sure it works...
> USE tempdb
> GO
> SET nocount ON
> GO
> CREATE TABLE book (
> bookid INT IDENTITY( 1 , 1 ) NOT NULL PRIMARY KEY
> , category VARCHAR(10) NULL
> , booktype VARCHAR(10) NULL
> , author VARCHAR(10) NULL)
> GO
> INSERT book
> VALUES('fiction'
> , 'paperback'
> , 'twain')
> INSERT book
> VALUES('fiction'
> , 'hardbound'
> , 'asimov')
> INSERT book
> VALUES('fiction'
> , 'paperback'
> , 'rand')
> GO
> CREATE PROCEDURE getbook
> @.category VARCHAR(10) = NULL
> , @.bookType VARCHAR(10) = NULL
> , @.author VARCHAR(10) = NULL
> AS
> SELECT category
> , booktype
> , author
> FROM book
> WHERE (category = @.category
> OR @.category IS NULL )
> AND (booktype = @.bookType
> OR @.bookType IS NULL )
> AND (author = @.author
> OR @.author IS NULL )
> GO
> -- get all fiction books
> EXEC getbook @.category = 'fiction'
> -- all fiction books by rand
> EXEC getbook @.category = 'fiction' ,
> @.author = 'rand'
> -- all paperbacks
> EXEC getbook @.bookType = 'paperback'
> -- returns all books since we don't supply any filter
> EXEC getbook
> GO
> DROP TABLE book
> GO
> DROP PROCEDURE getbook
> GO
>

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
>

Conditional stored procedure question

I need to create a stored proc which has a conditional WHERE clause depending on the value of a passed parameter. I'm having trouble handling the condition. I'm missing something here.

CREATE PROCEDURE Milestone_Get
(@.myID int, @.iShowAll int)

AS

SELECT uid, name, date, registration_confirmed
FROM tbl_members

WHERE
If @.iShowAll = 0
begin
(uid = @.myID) AND (registration_complete = 0)
end
else
begin
(uid = @.myID)
end

GO

Thanks,
davidyou can use a CASE statement but i do not know the syntax..heres another way of doing it

CREATE PROCEDURE Milestone_Get
(@.myID int, @.iShowAll int)
AS
if @.iShowAll = 0
 SELECT uid, name, date, registration_confirmed FROM tbl_members where uid = @.myID AND registration_complete = 0
else
 SELECT uid, name, date, registration_confirmed FROM tbl_members where uid = @.myID
go

HTH|||Here's an example using a Case

SELECT uid, name, date, registration_confirmed FROM tbl_members
WHERE (uid = @.myID) AND registration_complete = CASE WHEN @.iShowAll = 0 THEN 0 ELSE registration_complete END

Conditional sorting in order by clause

Hi,
I have a query as

select name, age, address from employee order by name

Now i want to do sorting as ASC or DESC in order by clause dynamically.

I tried something like this :-

declare @.Order int
set @.Order = 1

select name, age, address from employee
order by name
CASE
WHEN @.Order = 0 THEN ASC
WHEN @.Order = 1 THEN DESC
END

But its giving me error, Is it correct or is there any other way to do conditional sorting?

order by is usually the last statement in a query and u cant do it this way...simple way is use if-else ...

if(@.order=1)

select ...order by name desc

else

select ...order bu name asc

u may try to use dynamic sql and achive it too, but its not adviseable...

|||

Use the following query it is a conditional sorting...

Declare @.Order int
Set @.Order = 1

Selecct name, age, address from employee
Order By
CASE WHEN @.Order = 0 THEN Name End ASC,
CASE WHEN @.Order = 1 THEN Name End DESC

Conditional Parameter in Where Clause

I'm trying to figure out a way to filter a dataset using a parameter only when the user enters a value for the parameter and to not apply the filter if the parameter is left blank (or null) by the user. I would like to do this within the WHERE clause of the SELECT statement to minimize the size of the dataset whenever possible. Is there such a thing as a default parameter value that equates to "any value"?

Nothing I have tried works (but I'm new to SQL, Report Server and the Visual Basic Development Environment).

Thanks in advance,

Chris

Rather than leaving the parameter unselected, you need to add an option with a value of NULL and text that matches your scenario e.g. blank, "All", "N/A", "Unspecified" etc. To do this you'll need to modify the query for the paramter dataset to:

SELECT id = NULL, name = 'All'
UNION ALL
SELECT id, name
FROM param_table

Then update your main query with the following WHERE clause

WHERE id = ISNULL(@.param, id)

so when the null option is selected the WHERE clause equates to id=id which is always true and hence all rows are returned.

Hope this helps.

|||

Thanks Adam,

I was not familiar with ISNULL. I got it to work sort of like I wanted it to by checking the "Allow Null Value" checkbox and making the default value NULL in the Report Parameters dialog box and then putting this in the WHERE clause:

WHERE LITEM.SIZE = ISNULL(@.Input_Size, LITEM.SIZE)

However, I could not figure out where to put the following statement (everything I tried resulted in an error - but I'm probably missing something obvious):

SELECT id = NULL, name = 'All'
UNION ALL
SELECT id, name
FROM param_table

...and therefore, the user must uncheck the NULL checkbox in order to enter a filter value and it's not real obvious that when NULL is checked, that the filter is not applied.

Thanks again for pointing me in the right direction!

Chris

|||

By your response it seems like your parameter is a textbox the user types into, is that correct?

My prerred way is to present the user a list of options i.e. a dropdown. In that case you don't get a null checkbox. The options in the dropdown can either be typed in on the paramter screen or can come from a dataset. The SELECT statement I provided is meant as an example of query used to populate such a dataset i.e. it includes a NULL option.

If you wish to use a textbox then you could alter your SQL query and rather than using ISNULL you could use an OR in your WHERE clause as follows

WHERE LITEM.SIZE = @.Input_Size
OR @.Input_Size = '' -- empty string

If LITEM.SIZE and @.Input_Size are integers then it gets a little more complicated. You'll need to experiment.

|||

Adam,

Thanks! It's now working just the way I wanted it to!

Chris Heitman