GetDate () causes an error when used with EXEC

Question

MS SQL Server 2008.

Why is this syntax normal:

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 around the "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 variable, and not something else to EXEC - in particular, you cannot have any form of complex expression or function call ¹ . Therefore, if you want to calculate the value, you need to put this 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 

In addition, you should avoid naming stored procedures starting with sp_ :

Avoid using the sp_ prefix in the naming procedure. This prefix is ​​used by SQL Server to refer to system procedures. Using a prefix can lead to a break in the application code if there is a system procedure with the same name.

---

¹ Martin points out that some functions can be called, but this is the exception rather than the rule, therefore, although my statement is not 100% accurate, considering it doesn’t harm you.