Can’t Use Inline Function Calls While Calling a Stored Procedure

I can’t believe it has been more than 18months since I have written a blog.

Today, I was trying to call a stored procedure in SQL Server 2008 and keep getting Incorrect syntax error.

For example, let’s say, I have a following stored procedure on the asp.net membership provider that returns active users since a given date:

CREATE PROCEDURE _GetActiveUserSinceDate

@SinceDate DATETIME

AS

BEGIN

SET NOCOUNT ON;

SELECT *

FROM [dbo].[aspnet_Users]

WHERE [LastActivityDate] >= @SinceDate

END

And when, I try to call this stored procedure as follows to get me the list of users logged in the last 7 days:

EXEC [dbo].[_GetActiveUserSinceDate] DATEADD(d, -7, GETDATE())

I would get the following error message:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'd'.

I was able to get around this error message by explicitly assigning the return value of DATEADD function to a variable and passing that variable to my stored procedure as shown below:

DECLARE @SinceDate DATETIME

SET @SinceDate = DATEADD(d, -7, GETDATE())

EXEC [dbo].[_GetActiveUserSinceDate] @SinceDate

I keep thinking of why do I need to have this extra variable and why can’t use the function inline and so searched on MSDN and found the following page:

http://msdn.microsoft.com/en-us/library/ms189915.aspx

Look under the “Specifying Parameters” section and you see the following statement:

The supplied value must be a constant or a variable; you cannot specify a function name as a parameter value. Variables can be user-defined or system variables such as @@spid.

So there you have it and it is my bad to use the same mindset while writing TSQL as I do while writing C# code. If you already know about this, you are a lot a better TSQL programmer than me.

Thanks for reading.

Sonny