No formulas in Stored Procedure params?

Hi,
is there any reason why it is not allowed to use formulas as Stored
Procedure parameters?
e.g.: EXEC myProc(@.myVar + ' ' + @.myVar2)
I could make use of this feature over and over if SQL Server 2k would allow
me to.
Axel DahmenI believe this has to do with the way the compiler works. By the time
you've hit the EXEC, it's too late to evaluate the expression. But it's
really not too bad:
DECLARE @.myVar3 VARCHAR(300)
SET @.MyVar3 = @.myVar + ' ' + @.myVar2
EXEC myProc @.MyVar3
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Axel Dahmen" <NO_SPAM@.NoOneKnows.invalid> wrote in message
news:uDecFY3PFHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> is there any reason why it is not allowed to use formulas as Stored
> Procedure parameters?
> e.g.: EXEC myProc(@.myVar + ' ' + @.myVar2)
> I could make use of this feature over and over if SQL Server 2k would
allow
> me to.
> Axel Dahmen
>|||Thanks, Adam.
I must admit that I'm not too satisfied with the current way of handling
this. I feel it's fairly awkward to write a bunch of lines like:
DECLARE @.myVar3 VARCHAR(300)
SET @.MyVar3 = @.myVar + ' ' + @.myVar2
EXEC myProc @.MyVar3
instead of just simply:
EXEC myProc @.myVar + ' ' + @.myVar2
particularly if you do things on-the-fly.
Do you perhaps know if SQL Server 2k5 will be able to process expressions in
procedure/UDF calls? If it doesn't, do you probably know about some channel
to suggest this functionality? It's nagging me for quite a while now. And
perhaps others might find a change in parsing procedure/UDF calls useful,
too.
Best wishes,
Axel
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> schrieb im
Newsbeitrag news:#5ZoPd3PFHA.3196@.TK2MSFTNGP12.phx.gbl...
> I believe this has to do with the way the compiler works. By the time
> you've hit the EXEC, it's too late to evaluate the expression. But it's
> really not too bad:
> DECLARE @.myVar3 VARCHAR(300)
> SET @.MyVar3 = @.myVar + ' ' + @.myVar2
> EXEC myProc @.MyVar3
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Axel Dahmen" <NO_SPAM@.NoOneKnows.invalid> wrote in message
> news:uDecFY3PFHA.648@.TK2MSFTNGP14.phx.gbl...
> allow
>|||> I feel it's fairly awkward
But necessary, so what are you going to do?
A workaround would be dynamic SQL, but it becomes quite complex as you have
varying data types being passed to the stored procedure.

> Do you perhaps know if SQL Server 2k5 will be able to process expressions
> in
> procedure/UDF calls?
No.

> If it doesn't, do you probably know about some channel
> to suggest this functionality?
sqlwish@.microsoft.com

> It's nagging me for quite a while now. And
> perhaps others might find a change in parsing procedure/UDF calls useful,
> too.
Frankly, the known workarounds are really not all that cumbersome, and I
think most people have just learned to live with the limitation and have
moved on. This sounds like the same kind of problem we have when people use
SELECT * or otherwise try avoid "all the hard work" of typing out a column
list.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||"Axel Dahmen" <NO_SPAM@.NoOneKnows.invalid> wrote in message
news:uOU1kA4PFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Do you perhaps know if SQL Server 2k5 will be able to process expressions
in
> procedure/UDF calls? If it doesn't, do you probably know about some
channel
> to suggest this functionality? It's nagging me for quite a while now. And
> perhaps others might find a change in parsing procedure/UDF calls useful,
> too.
No, the behavior won't change in SS2005 (at least, not based on anything
I've heard, and I've also just tested on the latest build -- same behavior).
I believe -- although someone else can hopefully back me up on this as I
don't know for certain -- that the behavior complies to the ANSI standard.
And if the standard doesn't support that, I wouldn't expect SQL Server to.
As I said, it has to do with the way the compiler works. SQL is compiled
using a very simple single-pass method. Re-doing that compilation for the
sake of this feature is not really a good use of time. There are a few
other features that could be done with multi-pass compilation, but I don't
think the SQL Server team would agree with me on how interesting I think
some of them are ;)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--

> Best wishes,
> Axel
>
> --
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> schrieb im
> Newsbeitrag news:#5ZoPd3PFHA.3196@.TK2MSFTNGP12.phx.gbl...
>|||I might be ignorant here, but even after giving it some deep thoughts I
don't find a reason why it should give the interpreter a hard time to accept
expressions instead of identifiers as procedure parameters.
The internal SQL Server part executing the SET statement could be used, for
instance, to interpret a given expression. The interpreter might create some
temporary dummy variable first to store the calculation result internally.
When calling a procedure/UDF a data type transformation call is triggered
internally for each parameter. So it's safe to assume that a parameter's
data format is already known to the interpreter. It should be easy then to
create such internal dummy variable to store the expression result and to
forward that result to the standard procedure/UDF call.
It would even be possible to have no additional execution costs compared to
the present (lacking)implementation. A cache could be implemented to hold
all temporary variables for the next call to the same procedure within the
same batch.
Program Flow:
- prepare procedure call
- WHILE parameter read
- if parameter is no legal identifier then
- create dummy variable to hold parameter
- SET {dummy variable} = parameter
- overwrite parameter to point to dummy variable
- add parameter to procedure call
- call procedure
RFC,
Axel Dahmen
www.sportbootcharter.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> schrieb im
Newsbeitrag news:#OH8lR4PFHA.2560@.TK2MSFTNGP14.phx.gbl...
> "Axel Dahmen" <NO_SPAM@.NoOneKnows.invalid> wrote in message
> news:uOU1kA4PFHA.2604@.TK2MSFTNGP10.phx.gbl...
expressions
> in
> channel
And
useful,
> No, the behavior won't change in SS2005 (at least, not based on
anything
> I've heard, and I've also just tested on the latest build -- same
behavior).
> I believe -- although someone else can hopefully back me up on this as I
> don't know for certain -- that the behavior complies to the ANSI standard.
> And if the standard doesn't support that, I wouldn't expect SQL Server to.
> As I said, it has to do with the way the compiler works. SQL is compiled
> using a very simple single-pass method. Re-doing that compilation for the
> sake of this feature is not really a good use of time. There are a few
> other features that could be done with multi-pass compilation, but I don't
> think the SQL Server team would agree with me on how interesting I think
> some of them are ;)
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
it's
would
>|||>> I might be ignorant here, but even after giving it some deep
thoughts I don't find a reason why it should give the interpreter a
hard time to accept expressions instead of identifiers as procedure
parameters. <<
In the early days of Sybase/SQL Server, we had a rule that you never
wrote over one page of T-SQL in a stored procedure or a trigger. It
was a kludge to get around the lack of DRI actions, DEFAULT, CHECK(),
etc, back in the Stone Age. It was never meant to be a full language.
The truth is that T-SQL was an early, one-pass compiler based on C; it
was not meant to do Thunks. One -pass compilers have a hell of a time
with Thunks; it takes two passes in most cases. Today T-SQL would be a
one-quarter senior project using a Compiler compiler, YACC or whatever.
Full strength application development languages came later. The
Informix 4GL isbased on the Algol family and a lot of mainframe
packages use it under the covers; it generated ANSI Standard C and is
very portable, has a report writer, etc. Progress started as a nice
application development 4GL which added SQL. PL/SQL in Oracle and the
Standard SQL/PSM are both based on ADA.
What we need is the Standard SQL/PSM instead of the CLR crap that is
about to destroy SQL Server databases (I have some anti-CLR rants
posted in various places).|||> The truth is that T-SQL was an early, one-pass compiler based on C; it
> was not meant to do Thunks. One -pass compilers have a hell of a time
> with Thunks; it takes two passes in most cases.
I appreciate that. But the SET command is already able do it. Thus I assume
the only change in procedure call processing should basically be to call the
SET command part after reading a parameter token. Perhaps there even is a
general expression parser part available in the interpreter that can be used
by T-SQL language coders at MS.
My main assumption is that:
if the T-SQL interpreter can process this (manual) sequence in one pass
DECLARE @.var ...
SET @.var = {expression}
EXEC proc @.var
then it can also do this automatically itself.
The most simple (and farmost worst) option would be to just expand any
parameter call to the above sequence internally. This deduction is my proof
of concept.

> What we need is the Standard SQL/PSM instead of the CLR crap that is
> about to destroy SQL Server databases (I have some anti-CLR rants
> posted in various places).
That's another story, but you're right, I agree with you. Giving too much
programming power to SQL Server will probably lead into decadence in design
in terms of a multi-tier model. And I'm also apprehensive of SQL Server 2005
becoming a slow performer and thus less scalable. Any Framework,
particularly .NET and Java by its nature of using IL, is slow and clumsy.
Well, it's a matter of consideration between RAD and performance. But enough
OT... ;)
Axel|||>> if the T-SQL interpreter can process this (manual) sequence in one
pass ... then it can also do this automatically itself. <<
Did you take a compiler writing course in college? I taught one :)
The simple ase is easy; the general case is a bear!
The compiler has to catch the general expression in the parameter,
adjust the scope of the declarations, fill in parameters in any
function calls, recursive function calls, self-references, etc. It
is not a simple matter of replacing a marker with "((<< expression
T-SQL sets error flags one statement at a time; SQL/PSM and modern
languages set multiple errors all at once based on BEGIN ATOMIC .. END
blocks. T-SQL was never meant for "industrial usage" in a modern
programming environment.|||> One -pass compilers have a hell of a time
> with Thunks; it takes two passes in most cases.
It took a few moments for me to see what you mean... The trick would be to
assume a destination data type to store the expression result in.
I guess there will be a feasible solution to that. For instance, every
expression calculated by SQL Server has an intrinsic data type. During
interpretation of the SET command the result is probably forwarded to the
data type converter part after calculation of the expression result. To
create a matching temporary memory space (or variable), this step ought to
be injected in between:
Assumed Standard SET Command Program Flow:
1) read destination variable
2) lookup destination variable in memory
3) read expression to be assigned
4) calculate expression
5) forward expression result and destination variable to
data type converter in order to store the converted
result into destination variable
To allow for dynamic destination variable creation, the flow could be
interjected with something like (one additional step added):
1) read expression to be assigned
2) calculate expression
* 3) create temporary destination variable from expression
data type
4) forward expression result and destination variable to
data type converter in order to store the converted
result into destination variable
5) provide destination variable to stored procedure
parameter
Regards,
Axel