158

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();
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nat
  • 14,175
  • 5
  • 41
  • 64

3 Answers3

206

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;
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
27

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'

Alexander
  • 7,484
  • 4
  • 51
  • 65
0

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;
eglease
  • 2,445
  • 11
  • 18
  • 28