Functions in Parameter Definitions

I have a pair of reports that are the same except that one takes date parameters and another runs for the calculated lst full month. Currently they run off of separate stored procedures, but I would like to combine them. I don't know if my reporting tool will handle it or not, but I want to test it, so I need to combine the 2 sp's into one. I thought the first thing to try would be to set the default values to the start and end date of the previous month, like so:

CREATE PROCEDURE [dbo].[usp_blahblah]

(

@.StartDate SmallDateTime = DateAdd(mm,-1,DateAdd(mm,DateDiff(mm,0,GetDate()),0)), -- 1st of Last Month

@.EndDate SmallDateTime = DateAdd(ms,-3,DateAdd(mm, DateDiff(mm,0,GetDate()),0)) -- End of Last Month

)

..but it won't parse ("Incorrect syntax near '('."). So I'm thinking that you can't use a function in the definition of a param, although I can't find any documentation.

I'm sure there are other approaches, but I thought this would be the most straight forward... Does anybody have a really elegant idea?

From the CREATE PROCEDURE documentation - "Is a default value for the parameter. If a default is defined, the procedure can be executed without specifying a value for that parameter. The default must be a constant or it can be NULL."

Just make the default value NULL, then test to see if the parameters are NULL and set them in the body of the proc. Like this

CREATE PROCEDURE [dbo].[usp_blahblah]
(
@.StartDate SmallDateTime = NULL,
@.EndDate SmallDateTime = NULL)
IF @.StartDate IS NULL AND @.EndDate IS NULL
BEGIN
SET @.StartDate = DateAdd(mm,-1,DateAdd(mm,DateDiff(mm,0,GetDate()),0)), -- 1st of Last Month
SET @.EndDate = DateAdd(ms,-3,DateAdd(mm, DateDiff(mm,0,GetDate()),0)) -- End of Last Month
END

|||That's slick. I'll give it a try and hope Crystal can handle it!|||

Be careful using the above approach though if you are using the variables further in a query. The query optimizer can do parameter sniffing to get optimal plan based on the parameter values but if you modify it within the SP then the sniffing cannot happen. So if you need to modify the parameter values then move the actual query into another SP and pass the modified parameters to that instead. See the link below for more details on how plan caching works.

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx