SQL function as default parameter value?
SqlSql ServerStored ProceduresSql Problem Overview
I tried changing a default parameter value with this:
ALTER PROCEDURE [dbo].[my_sp]
@currentDate datetime = GETDATE()
and all the SQL pre-compiler gave me was this error:
> Msg 102, Level 15, State 1, Procedure my_sp, Line 8 Incorrect syntax near '('.
I have already created the procedure. (I'm not sure if that's relevant.) I was using a null default value and checking for that later, but that doesn't seem proper. Can I do this in one line?
Update: I was going off of [MSDN's description of stored procedure parameters][1]:
> [ = default ] Is a default value for the parameter. If a default value is defined, the function can be executed without specifying a value for that parameter.
>>Note:
Default parameter values can be specified for CLR functions except for the varchar(max) and varbinary(max) data types.
> When a parameter of the function has a default value, the keyword DEFAULT must be specified when the function is called to retrieve the default value. This behavior is different from using parameters with default values in stored procedures in which omitting the parameter also implies the default value.
Am I reading this wrong?
Many thanks.
[1]: http://msdn.microsoft.com/en-us/library/ms186755.aspx "CREATE FUNCTION (Transact-SQL)"
Sql Solutions
Solution 1 - Sql
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()
Solution 2 - Sql
I don't think that is possible, you have to use a literal (constant) value as the default.
However you can do this:
Set @currentDate = Coalesce(@currentDate , GetDate())
Solution 3 - Sql
You can try as follow:
Set @CurrentDate=IsNull(@CurrentDate,GetDate())
Solution 4 - Sql
I infer you're using Microsoft SQL Server from the square brackets in your example.
From MSDN:
> Only a constant value, such as a > character string; a scalar function > (either a system, user-defined, or CLR > function); or NULL can be used as a > default.
The function GETDATE() returns a different value from time to time, so it is not a constant expression.
Solution 5 - Sql
That value is not deterministic and cannot be used
Solution 6 - Sql
Suggestion:
Set the default to NULL
Do the Default GETDATE() in the front end.