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: