SQL Server SP Parameter Default Value

SQL Server does not allow creating a stored procedure with parameter default values that are not constant. This can be a problem if you want to give a parameter a default value that is predictable but not constant such as giving a datetime parameter a default value of the current time.

There are two ways to resolve this. One is solution but it's not very flexible. The other is a workaround but can be used in more situations. First the solution. If the parameter is used as a minimum or maximum value check, you can totally ignore the value if it's null.

Here is an example of the first solution. Let's say we want to write a stored procedure to get all records from an Activity table that occured from one point in time to another. In this case we want to include all records if the boundry variable is not specified. In the example below we simply do this by checking for NULL and using the logical OR.

CREATE PROCEDURE GetActivityRecords
@From DATETIME
@TO DATETIME

AS
 BEGIN
   SELECT * FROM Activity
   WHERE (@From is null or LogTime >= @From)
   and (@To is null or LogTime <= @To)
 END

Another way to do this that is more flexible is to check if the parameter is null and in that case set it equal to whatever value you wish.

For example, in the code below I check if the @To variable is null and if it is I set it equal to the current time.

CREATE PROCEDURE GetActivityRecords
@From DATETIME
@TO DATETIME

AS
 BEGIN
   IF @To is null
   BEGIN
      SET @To = GetDate()
   END

   SELECT * FROM Activity
   WHERE (@From is null or LogTime >= @From)
   and LogTime <= @To
 END