Stored Procedure Parameter Default Value - Is This a Constant or a Variable

Stored Procedure parameter default value - is this a constant or a variable

It has to be a constant - the value has to be computable at the time that the procedure is created, and that one computation has to provide the value that will always be used.

Look at the definition of sys.all_parameters:

default_value sql_variant If has_default_value is 1, the value of this column is the value of the default for the parameter; otherwise, NULL.

That is, whatever the default for a parameter is, it has to fit in that column.

---

As Alex K pointed out in the comments, you can just do:

CREATE PROCEDURE [dbo].[problemParam] 
    @StartDate INT = NULL,
    @EndDate INT = NULL
AS  
BEGIN
   SET @StartDate = COALESCE(@StartDate,CONVERT(INT,(CONVERT(CHAR(8),GETDATE()-130,112))))

provided that NULL isn't intended to be a valid value for @StartDate.

---

As to the blog post you linked to in the comments - that's talking about a very specific context - that, the result of evaluating GETDATE() within the context of a single query is often considered to be constant. I don't know of many people (unlike the blog author) who would consider a separate expression inside a UDF to be part of the same query as the query that calls the UDF.

Stored procedure parameter default value

Try this..

I put only one parameter because the others depend on it.

First approach

USE ..
GO
..
ALTER PROCEDURE yourSP
(@mydateParam DATETIME)

AS
BEGIN ...
SELECT @mydate = @mydateParam
SELECT @MT = DATEPART(MONTH,@mydate)
SELECT @MY =  DATEPART(YEAR,@mydate)
  ---
SELECT * FROM ....

To call the SP:

DECLARE @mydateParam DATETIME
SELECT @mydateParam = GETDATE()
execute yourSP @mydateParam

Second approach

ALTER Procedure yourSP
@mydateParam datetime = null
AS
BEGIN ...
IF @mydateParam is null
SET @mydateParam = getdate()
SELECT @mydate = @mydateParam
SELECT @MT = DATEPART(MONTH,@mydate)
SELECT @MY =  DATEPART(YEAR,@mydate)

   ---
SELECT * FROM ....

To call the SP:

DECLARE @mydateParam DATETIME
SELECT @mydateParam = GETDATE()
execute yourSP @mydateParam

or

execute yourSP

Why can't GETDATE() be used as the default value of a procedure parameter or a value in an EXECUTE statement?

This is covered in the documentation, CREATE PROCEDURE (Transact-SQL), under the default subheading in the arguments section:

A default value for a parameter. If a default value is defined for a
parameter, the procedure can be executed without specifying a value
for that parameter. The default value must be a constant or it can be
NULL. The constant value can be in the form of a wildcard, making it
possible to use the LIKE keyword when passing the parameter into the
procedure.

Emphasis mine.

GETDATE() is not a constant, so cannot be used an a DEFAULT value. Hence why you need to use the format below, as then the value of GETDATE() is determined at run time:

CREATE PROC YourProc @Param date = NULL
AS

    IF @Param IS NULL BEGIN
        SET @Param = GETDATE();
    END;
    ...

Adding a default value to optional parameter of stored proce

As Kritner said above, the default value of parameters can be only a constant.
However, if the NULL value does not have another meaning in this context, you can use something like this:

CREATE PROCEDURE sp_Transactions1
     @earliestDate date=NULL
AS
IF @earliestDate IS NULL 
    SET @earliestDate=CONVERT(DATE,
        STR(YEAR(DATEADD(m,-3, getdate())),4)
        +RIGHT('0'+CONVERT(VARCHAR(2),MONTH(DATEADD(m,-2, getdate()))),2)
        +'01'
    )
--...

Is the default value of a declared variable always null?

In MySQL:

referred to MySQL documentations: https://dev.mysql.com/doc/refman/8.0/en/declare-local-variable.html

To provide a default value for a variable, include a DEFAULT clause. The
value can be specified as an expression; it need not be a constant. If
the DEFAULT clause is missing, the initial value is NULL.

In SQL Server

As microsoft says: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/variables-transact-sql?view=sql-server-2017

When a variable is first declared, its value is set to NULL.

But the story is a bit different when I have a table variable. suppose that I have a table variable like:

DECLARE @testTable AS TABLE
(
    Code INT,
    Firstname VARCHAR(100)
);

In this case we can not say that the @testTable is a variable which is initialised with NULL. In table variables, when they are declared, they are an empty table which contains no data.

SQL Server: Optional variable in a stored procedure

Providing a default value to the stored procedure parameter will make it optional.

EDIT:

CREATE PROC [ EDURE ] [ owner. ]

procedure_name [ ; number ]

[ { @parameter data_type }

[ VARYING ] [ = default ] [ OUTPUT ]

] [ ,...n ]

default

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.
It can include wildcard characters (%,
_, [], and [^]) if the procedure uses the parameter with the LIKE keyword.

Please see SQL Server Documentation: Specifying Parameter Default Values

SQL function as default parameter value?

Default value for stored procedures parameter have to be constants.
You'd need to do the following...

ALTER Procedure [dbo].[my_sp]
@currentDate datetime = null
AS
IF @currentDate is null
SET @currentDate = getdate()

Current value of a Sequence as the default parameter for a stored procedure

You cannot put a SELECT statement in DEFAULT parameter definition for your stored procedure. The workaround for you is to set NULL as DEFAULT value, and then check if value of @SyncId is null => assign it the current value from your sequence.

ALTER PROCEDURE [dbo].[sp_GET_TransformationSummary]
@AreaCode           AS NVARCHAR(MAX),
@SyncId             AS INT = NULL

AS
BEGIN
SET NOCOUNT ON;

IF @SyncId IS NULL 
    SELECT @SyncId = CONVERT(INT, CURRENT_VALUE) FROM SYS.SEQUENCES WHERE name = 'SQ_COMMON'

SELECT * FROM TABLE_NAME
WHERE Area= @AreaCode AND SyncId = @SyncId;

END

Should local variables for constants be avoided in stored procedures?

As documented in the article Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

If you use a local variable in a query predicate instead of a
parameter or literal, the optimizer resorts to a reduced-quality
estimate, or a guess for selectivity of the predicate. Use parameters
or literals in the query instead of local variables

Regarding your questions...

I was also under the impression that SqlServer automatically convert "magic numbers" into variables in order to reuse plans.

No, never, it can auto parameterise adhoc queries but parameters behave differently from variables and can be sniffed. By default it will only do this in very limited circumstances where it is "safe" and unlikely to introduce parameter sniffing issues.

Is there a difference between using a "magic number" and a local
variable?

Yes, the statement is generally compiled before the variable value is even assigned. And even if the statement was to be subject to deferred compilation (or happen to be recompiled after the assignment) values of variables are still never sniffed except if you use option (recompile). If you use the literal inline SQL Server can look up that literal value in the histogram and potentially get much more accurate estimates rather than resorting to guesses. Accurate row estimates are important in getting the correct overall plan shape (e.g. Join type or access method selection) as well as getting an appropriate memory grant for your query.

The book "SQL Server 2005 practical troubleshooting" has this to say on the issue.

In SQL Server 2005, statement level compilation allows for compilation
of an individual statement in a stored procedure to be deferred until
just before the first execution of the query. By then the local
variable's value would be known. Theoretically SQL Server could take
advantage of this to sniff local variable values in the same way that
it sniffs parameters. However because it was common to use local
variables to defeat parameter sniffing in SQL Server 7.0 and SQL
Server 2000+, sniffing of local variables was not enabled in SQL
Server 2005. It may be enabled in a future SQL Server release though

(NB: this has not in fact been enabled in any version to date)

If yes, is it only in stored procedures or does it also
apply to ad-hoc queries and dynamic sql?

This applies to every use of variables. Parameters can be sniffed though so if you were to have a variable in the outer scope passed as a parameter in the inner scope that would allow the variable value to be sniffed.

Is it a bad habit to use local variables like I do?

If the plan is going to be sensitive to the exact variable value than yes. There are certain places where it will be entirely innocuous however.

The disadvantage of option (recompile) as a fix is that it recompiles the statement every time. This is unnecessary when the only reason for doing so is to get it to sniff a variable whose value is constant. The disadvantage of option (optimize for) with a specific literal value is that if the value changes you need to update all those references too.

Another approach would be to create a view of Constants.

CREATE VIEW MyConstants
AS
SELECT 3 AS OrderTypeCash, 4 AS OrderTypeCard

Then, instead of using a variable at all for those, reference that instead.

WHERE [Order].OrderType = (SELECT OrderTypeCash FROM MyConstants)

This will allow the value to be resolved at compile time and only need to be updated in one place.

Alternatively, if you use SSDT and database projects you could use a sqlcmd variable that is defined once and assigned to and then replace all your TSQL variable references with that. The code deployed to the server will still have "magic numbers" but in your source code it is a single SqlCmd variable (NB: For this pattern you might need to create a stub procedure in the project and use the post deployment script to actually alter it with the desired definition and performing the sqlcmd substitutions).

---

Related Topics