CODEWITHSUNDEEP
sqlsql-serversyntax
Don
Don

Reputation: 49

Conditional statement in WHERE clause

I need to write a conditional statement in my where clause that uses different operators based on the parameter passed into the procedure. I can't seem to find the syntax that will work.

My example is as follows:

@DateValue datetime

select * 
from table
where field1 = 'x'
and field2 = 'y'
and if @DateValue = '1/1/1900' then
  field3 <= getdate()
else
  field3 = @DateValue
end

Thanks for everyone's assistance.

Upvotes: 0

Views: 931

Answers (2)

LongChalk_Rotem_Meron
LongChalk_Rotem_Meron

Reputation: 803

I think you would need something like (psuedo-code)

Create PROCEDURE GetPartialTable
@DateValue datetime
   AS
Begin

IF (@DateValue = '1900-01-01') 
select * from table where field1 = 'x' and field2 = 'y'  and field3 <= getdate();
else
  select * from table where field1 = 'x' and field2 = 'y'  and field3 = @DateValue;
END IF

END

It sounds like the solution above, but the results might be seriously different if the context is more general, e.g. if the entire SQL statement is completely different.

Upvotes: 0

Jimmy
Jimmy

Reputation: 91472

and ((@DateValue = '1/1/1900' and field3 <= getdate()) or 
     (@DateValue <> '1/1/1900' and field3=@DateValue))

Upvotes: 9

Related Questions