April 25, 2011 at 1:30 pm
Hi,
If I execute:
SELECT DATEADD(m,-6,GETDATE())
it returns 2010-10-25 15:23:30.637
But if I use this as a parameter to a stored procedure:
Exec PurgeOldData DATEADD(m,-6,GETDATE())
I get the error - Line 1: Incorrect syntax near 'm'.
I could simply pass a 6 and change the stored procedure to do the DATEADD calc but I was wondering why SQL doesn't resolve the function and pass the results to the SPROC. Am I doing something wrong?
Thanks
April 25, 2011 at 1:40 pm
To call a stored proc, SQL Server requires a certain 'syntax' when passing in the parameters. And by using the function inline like that, the parser is unable to determine what pieces are to be passed to the proc, what pieces are to be resolved before passing it to the proc, and which pieces are syntax issues. In this case, its a syntax issue because the way this is written violates the allowable syntax as described in Books Online (BOL) for calling a proc.
You could either build a sql string and resolve your value before calling the proc, or better yet, like you said, pass in a 6 and have the stored proc do the math.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
April 25, 2011 at 1:43 pm
T-SQL doesn't allow functions in exec statements that way. You can either pre-calculate the value and store it in a variable, then use the variable in the exec statement, or you can have the stored procedure do the math on the value. Either way, it has to be a value, not a formula, in the exec statement. It also won't allow inline queries in exec statements.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 25, 2011 at 5:43 pm
Thanks Guys,
I guess I was used to most of the programming languages I work in. I simply moved the function call into the stored procedure and pass the value as an integer. I appreciate the time you took to respond and give me your input.
April 26, 2011 at 7:03 am
I wish this did work that way. Learned years ago it doesn't, by making that same "mistake". I think what I did was try inline sub-queries instead of functions, and it didn't like them for me either. Would be nice if it could parse them out.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply