Maybe I am having a moment of 'afternoon', but can anyone explain why I get
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.
When running
CREATE PROC DisplayDate
(@DateVar DATETIME)
AS
BEGIN
SELECT @DateVar
END
GO
EXEC DisplayDate GETDATE();
You can't pass in a function call as an argument to your stored procedure. Instead use an intermediate variable:
DECLARE @tmp DATETIME
SET @tmp = GETDATE()
EXEC DisplayDate @tmp;
As Mitch Wheat mentioned you can't pass a function.
If in your case you should pass in a precalculated value or GETDATE() - you can use default value. For example, modify your stored procedure:
ALTER PROC DisplayDate
(
@DateVar DATETIME = NULL
) AS
BEGIN
set @DateVar=ISNULL(@DateVar,GETDATE())
--the SP stuff here
SELECT @DateVar
END
GO
And then try:
EXEC DisplayDate '2013-02-01 00:00:00.000'
EXEC DisplayDate
Remark: Here I supposed that NULL value is not in use for this parameter. If it is not your case - you can use another unused value, for example '1900-01-01 00:00:00.000'
the solution I found was to declare temp variables before executing and pass these into the execution line e.g.
Declare @ddate date
set ddate = getdate()
EXEC DisplayDate @ddate;
