SQL SERVER 2008R2 -- declaring optional parameter

I am using SQL SERVER 2008R2.

This works in my stored procedure body:

       DECLARE @timestamp datetime2(2)=CAST(CURRENT_TIMESTAMP AS datetime2(2))


BUT if I put it in the parameter listing of the stored procedure, it errors?

EXAMPLE:

ALTER PROCEDURE [dbo].[usp_MyStoredProcedure]
      @PASS_Options            char(1)='H'   -- DEFAULT value
      ,@timestamp datetime2(2)=CAST(CURRENT_TIMESTAMP AS datetime2(2))
      AS
BEGIN
END

ERROR MESSAGE:  Incorrect syntax near '('.
paultran00Asked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
You cannot use expressions as default value, only literals. Hence the name "default values", instead of "default expressions".

You can use a magic value. The most common is NULL:

ALTER PROCEDURE dbo.usp_MyStoredProcedure
    @option CHAR(1) = 'H' ,
    @timestamp DATETIME2(2) = NULL
AS
    SET NOCOUNT ON;

    SET @timestamp = ISNULL(@timestamp, CAST(CURRENT_TIMESTAMP AS DATETIME2(2)));

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.