Computed parameters in Stored Procedure

You know that you can create computed column in a table using other columns of the same table. Did you know that you can create a parameter in a stored procedure similar to that

Consider the following Stored Procedure

use tempdb
GO
Create procedure paramter_testing
(
@date_and_time datetime,
@date_only date = @date_and_time
)
as
select @date_and_time as date_and_time , @date_only as date_only

If you execute the above procedure by

EXEC parameter_testing '20101015 14:12:10'

THE result is

date_and_time               date_only
--------------------------   ------------
2010-10-15 14:12:10.000    2010-10-15

As you see the parameter value for @date_only is computed based on the other parameter @date_with_time and time part is truncated as it is of DATE datatype

Note : It is not possible to apply functions on the parameter at the time of declaration (ex @date_only Date=dateadd(month,1,@date_with_time), etc)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s