Let us say I have a requirement to pass the current date time to a stored procedure.
For example let us consider the following SP
CREATE PROCEDURE [dbo].[DisplayDate](@Curr_Date DATETIME)
AS SELECT @Curr_Date
When I try to execute it with the parameter as getdate(). I would receive the following error
exec [DisplayDate] getdate()
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Theorically it should have worked. There is a Limitation on the way we can use it with a stored procedures.
According to the Books online article on
Parameters (Database Engine)
http://technet.microsoft.com/en-us/library/ms190248(v=SQL.105).aspx
"When a stored procedure or function is executed, input parameters can either have their value set to a constant or use the value of a variable. Output parameters and return codes must return their values into a variable. Parameters and return codes can exchange data values with either Transact-SQL variables or application variables."
So it is clear that we cannot use any functions directly with an stored procedure.
So we have to look at other alternatives.
For example
The above stored procedure can be rewritten as
CREATE PROCEDURE [dbo].[DisplayDate]
AS SELECT Getdate()
So we need to consider the fact that we cannot pass the function directly to the stored procedure instead we have to either use it in variable or use the function directly.
For example let us consider the following SP
CREATE PROCEDURE [dbo].[DisplayDate](@Curr_Date DATETIME)
AS SELECT @Curr_Date
When I try to execute it with the parameter as getdate(). I would receive the following error
exec [DisplayDate] getdate()
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Theorically it should have worked. There is a Limitation on the way we can use it with a stored procedures.
According to the Books online article on
Parameters (Database Engine)
http://technet.microsoft.com/en-us/library/ms190248(v=SQL.105).aspx
"When a stored procedure or function is executed, input parameters can either have their value set to a constant or use the value of a variable. Output parameters and return codes must return their values into a variable. Parameters and return codes can exchange data values with either Transact-SQL variables or application variables."
So it is clear that we cannot use any functions directly with an stored procedure.
So we have to look at other alternatives.
For example
The above stored procedure can be rewritten as
CREATE PROCEDURE [dbo].[DisplayDate]
AS SELECT Getdate()
So we need to consider the fact that we cannot pass the function directly to the stored procedure instead we have to either use it in variable or use the function directly.
