Parameterized Stored Proc question

[timscronin]

I want to create a stored proc that might or might not recieve a value like below.

create proc cmine @vdate datetime = null
as
select * from table where mydate > @vdate

However if the value is null,( no value was supplied) I would not want to use the where clause. Should (or can you) use an if statement to check and see if no value was given to change the where clause?
Thanks

 

[tlbroadbent]

You can use an IF statement to control flow in a SQL script.

create proc cmine @vdate datetime = null
as

If @vdate Is Null
select * from table
Else
select * from table
where mydate > @vdate Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[DBCustard]

Or
select * from table
where (@vdate is null or mydate > @vdate)

This might be slightly eachier if you have lots of arguments of this flavour.