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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy