question

Katie 1 avatar image
Katie 1 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
sql-server-2008sql-serverstored-proceduresparameters
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
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;
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.