3.11 Default Parameters

There is one big problem with our stored procedure. It satisfies the new requirement for the business department but we've broken the customer application. We initially provided a stored procedure to the customer application developers that did not require an input parameter. If they've coded the stored procedure call in the client application, the call will fail with an error message stating that the input parameter is expected.

Instead of asking the application developers to change their code, we will create an input parameter that is "optional". If the parameter isn''t specified, the stored procedure will behave exactly like the first incarnation: it will compare the start date to the system time.

Note that in the declaration of the input parameter we include the equals sign and a value - in this case the value is null. This ensures that the stored procedure can be called without specifying the parameter, and SQL Server executes the stored procedure with a Null value. As developer you are responsible for doing something with the null variable.

In our stored procedure, we use Transact-SQL IF/ELSE logic to check for a Null value and run different SQL statements depending on the result.

Because this procedure is getting more complex, we use the -- syntax to add comments explaining what we're doing.

alter procedure get_product

(

@idtStartDate datetime = Null

)

as

 

-- If the calling application has provided a date, use it
-- Otherwise, check the product start date is before the system date.

 

IF @idtStartDate is NOT NULL
  select product_id, product_name, start_date
  from product
  where start_date >= @idtStartDate

 

ELSE

  select product_id, product_name, start_date
  from product
  where start_date <= getdate()

 

It's also possible to assign non-null values as default parameters. If the business rules demanded it, we could ensure that if the stored procedure is called without parameters that fixed dates are used. This example fixes the parameters to a particular year: create procedure (@ idtStartDate datetime = '1 Jan 2008' )... This is not really a practical example, its just to illustrate the functionality.