SQL Trying to use DATEADD in a stored procedure

Question

I've created a stored procedure in my example database on Azure:

CREATE PROCEDURE SalesLT.InsertOrderHeader(@OrderDate AS DATETIME=NULL, @DueDate AS DATETIME=NULL, @CustomerID AS INT=NULL)
AS
DECLARE @SalesOrderID INT = NEXT VALUE FOR SalesLT.SalesOrderNumber
IF @OrderDate IS NULL
    BEGIN
        SET @OrderDate = GETDATE()
    END

INSERT INTO SalesLT.SalesOrderHeader(SalesOrderID,OrderDate,DueDate,CustomerID,ShipMethod)
VALUES (@SalesOrderID,@OrderDate,@DueDate,@CustomerID,'CARGO TRANSPORT 5')

PRINT @SalesOrderID

This created fine but when I tried to call it I wanted a date a week from now:

EXEC SalesLT.InsertOrderHeader @DueDate= DATEADD(dd,7,getDate()) , @CustomerID=1

This didn't work. The errors say that where it says 'dd' it was expecting ( or select, and the same for the closing bracket of get date. What's wrong with it?

Possible duplicate of [Incorrect syntax near ')' calling storedproc with GETDATE](https://stackoverflow.com/questions/2399104/incorrect-syntax-near-calling-storedproc-with-getdate) — Esteban P., Sep 06 '17 at 14:42
You can't pass in a function call as an argument to your stored procedure. Instead use an intermediate variable https://stackoverflow.com/questions/2399104/incorrect-syntax-near-calling-storedproc-with-getdate — Esteban P., Sep 06 '17 at 14:42

score 5 · Accepted Answer · answered Sep 06 '17 at 14:42

EXEC:

    [ [ @parameter = ] { value   
                       | @variable [ OUTPUT ]   
                       | [ DEFAULT ]   
                       }  
    ]  
  [ ,...n ]

Note, carefully, that it accepts values and it accepts variables. What it doesn't accept is expressions.

You need to move the DATEADD logic to a separate line and store the resulting value in a variable.

DECLARE @dt datetime
SET @dt = DATEADD(day,7,getDate())
EXEC SalesLT.InsertOrderHeader @DueDate= @dt , @CustomerID=1

SQL Trying to use DATEADD in a stored procedure

1 Answers1