asked
optional paramenters in a procedure
i want to have optional paremeters in the procedure. I want give a user an oppurtunity to enter a one single day or a date range. Like the paremeter being 07/05/2011 or a date range of 07-05-2011 to 07-10-2011, is there a way that i can do it ? create procedure deletedata @transactionalday varchar begin as delete from atable where transactionalday = @transactionalday go
Just specify the default value for each parameter in the declaration... create proc deletedata @transactionalday varchar = null, @beginrange datetime = null, @endrange datetime = null, AS use them appropriately in the code... if @transactionalday is null begin delete from atable where transactionalday between @beginrange and @endrange; end else begin delete from atable where transactionalday = @transactionalday; end then call the procedure using the named parameters... exec deletedata @transactionalday = 'mytransactionalday'; or exec deletedata @begindate = mybeginningdate, @enddate = myendingdate;
Yes you can. Just add "= " for each optional parameter: create procedure deletedata @transactionalday varchar = '20110705' as begin delete from atable where transactionalday = @transactionalday end go When you call it you can specify a value for the optional params but if you don't their default value will be used instead.
