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
Create procedure paramter_testing
@date_and_time datetime,
@date_only date = @date_and_time
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)


Leave a Reply

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

You are commenting using your 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 )

Connecting to %s