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:
@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:
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
