GetDate() causes error when used with EXEC

Question

MS SQL Server 2008.

Why does this parse ok:

CREATE TABLE #Held
(
    Value decimal(18,4)
)
INSERT #Held EXEC dbo.sp_Held '2013-06-27', NULL

But this gives a syntax error(Incorrect syntax near 'day'.):

CREATE TABLE #Held
(
    Value decimal(18,4)
)
INSERT #Held EXEC dbo.sp_Held DATEADD(day, -1, getdate()), NULL

?

Answer 1

You can pass a literal value, or a variable, and nothing else to an EXEC - notably, you cannot have any form of complex expression or function invocation¹. So if you want to compute a value, you need to place that in a variable in a separate statement:

CREATE TABLE #Held
(
    Value decimal(18,4)
)
DECLARE @a datetime
SET @a = DATEADD(day, -1, getdate())
INSERT #Held EXEC dbo.sp_Held @a, NULL

Also, you should avoid naming stored procedures starting with sp_:

Avoid the use of the sp_ prefix when naming procedures. This prefix is used by SQL Server to designate system procedures. Using the prefix can cause application code to break if there is a system procedure with the same name.

---

¹ Martin points out that some functions can be invoked, but it's very much the exception rather than the rule, so whilst my statement isn't 100% accurate, treating it as so will not harm you.