March 12, 2013 at 5:49 am
I am using 2012 (but I think I have had similar issues with 2005 and 2008)
Why is it that the last two procedure calls, in the commented section, have syntax errors?
begin try drop procedure dbo.usp_WMELog end try begin catch end catch
go
create Procedure dbo.usp_WMELog
@event varchar(MAX)
as
insert into tblWMELog (event) Values (@event)
/*
declare @t int
declare @s-2 varchar(10)
set @t = 3
exec usp_WMELog @t
set @s-2 = cast (@t as varchar(10))
exec usp_WMELog @s-2
exec usp_WMELog cast (@t as varchar(10))
exec usp_WMELog convert(varchar(1), @t)
select * from tblWMELog
*/
Thanks.
<><
Livin' down on the cube farm. Left, left, then a right.
March 12, 2013 at 5:53 am
You can't apply functions to a parameter directly in the procedure call, you have to do it prior to the execute as you have with the first two calls.
March 12, 2013 at 5:56 am
Yeah, I got that part. 🙂 But do you know why? They both return "the converted type".
<><
Livin' down on the cube farm. Left, left, then a right.
March 12, 2013 at 5:59 am
T-SQL syntax rules, the parameter to a procedure cannot be a function.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2013 at 6:11 am
I see that, or the absence of it I guess. With a closer reading of the documentation I see it does not specify a function as a valid parameter. But I am still left with my question, better defined now to be sure, why does SQL Server not allow a function as a parameter?
<><
Livin' down on the cube farm. Left, left, then a right.
March 12, 2013 at 6:22 am
Tobar (3/12/2013)
I see that, or the absence of it I guess. With a closer reading of the documentation I see it does not specify a function as a valid parameter. But I am still left with my question, better defined now to be sure, why does SQL Server not allow a function as a parameter?
Why? I guess it's for the same reason that the white is not black, the sun is not a moon, and crocodiles do not fly (actually, they do, but at very low altitudes :hehe:)
It's just designed this way.
March 12, 2013 at 6:27 am
Probably just as Gail says - them's the rules!
I'd speculate that it's for separation - if you allow say getdate() to be passed, where do you stop? Entire queries to give a parameter value?
March 12, 2013 at 6:32 am
Thanks all.
<><
Livin' down on the cube farm. Left, left, then a right.
March 12, 2013 at 6:35 am
Tobar (3/12/2013)
But I am still left with my question, better defined now to be sure, why does SQL Server not allow a function as a parameter?
Why can I not create a table variable with select into?
Why does a procedure call have to start with EXEC (unless it's the first in the batch)?
Why are extra commas allowed in some places and not others?
Why does a UDF have to be called with it's schema when no other object requires that?
Why can I not pass columns as function parameters when using JOIN?
etc, etc, etc.
It's the syntax rules of the language.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2013 at 6:38 am
But why??? :w00t:
<><
Livin' down on the cube farm. Left, left, then a right.
March 12, 2013 at 7:09 am
Curiosity?
Then this great story is for you: http://www.classicreader.com/book/899/1/
I've already mentioned it last day...
March 12, 2013 at 7:29 am
Great story.
<><
Livin' down on the cube farm. Left, left, then a right.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply