asked on

SQL where clause problem

Hi

I have a parameter passed into a stored proc. If its a 0 I want a data less than a fixed date (called @YearEnd) and if its 1 I want data greater than @yearend

How do I do this, have been playing with case statements in the where clause, but getting a bit stuck.

Andy

Brian Crowe

...
WHERE
CASE @parameter
WHEN 0 THEN someDateColumn < @YearEnd
ELSE someDateColumn > @YearEnd
END

Lee

You can't in the way you want.

Either you do:

if @myparam = 0
begin
-- select statement here with one where clause
end
else
begin
-- a different select statement with another where clause
end

Open in new window

of you use dynamic sql

declare @sql nvarchar(1000), @option nvarchar(1), @yearend datetime

select @yearend = getdate() -- example value

if @myparam = 0
  select @option = '<'
else
  select @option = '>'

select @sql = 'select * from mytable where myvar ' + @option + ' ' + @yearend

exec (@sql)

Open in new window

ASKER CERTIFIED SOLUTION

QuinnDex

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Olaf Doschke

Is the query complicated? If not you could of course simply do

IF @param=0
SELECT ... WHERE datefield<@yearend
ELSE
SELECT ... WHERE datefield>@yearend

The CASE solution is also possible and of course is more elegant, as you don't duplicate the query itself and don't have to maintain both queries and keep them in sync aside of the < or >.

But you can't put datefield>@yearend as a THEN or ELSE expression, you can compute two different expressions, which result in >0, eg:

SELECT.... WHERE CASE WHEN @passedinparam=0
THEN datediff(d, datefield, @yearend)
ELSE datediff(d, @yearend, datefield)
END >0

You didn't specify the datefield name and the name if the parameter being 0 or 1, you can figure that out, I assume.

Bye, Olaf.

SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andy Green

ASKER

Thanks Guys

I have split the points I went with QuinnDex in the first instance, it worked but I ran into problems with adding further parameters, I was able to do this easily with mwvisa's solution.

I know I didn't mention the other params in my OP but I didn't want to over complicate things