Procedure parameters (expressions)

We're on the roll so let's continue with SQL Server's function and procedure parameters.

We've finished last post with:

declare @rv float
declare @v1 float = 2
declare @v2 float = 3
EXECUTE @rv = [dbo].[StupidFunction] @v1, @v2
print @rv
declare @exp bit = 1
EXECUTE @rv = [dbo].[StupidFunction] @v1, @v2, @exp
print @rv

but then you must:
- design everything to use this syntax right from the beginning
- you can't use this function in plain SQL
- you're getting yourself into other T-SQL problem with parameters which is a topic for another post

What exactly do I mean by T-SQL problem with parameters? Well... probably all T-SQL developers will tell me this is bollocks. Maybe it is, maybe it's not. It is for me, certainly. Maybe it's not a big deal but an irritating behaviour nevertheless.
The problem I'm referring here is inability to provide expressions as procedure parameters (or functions executed via EXECUTE statement). You can only use variables and constants (at least that)...

In real life, you're forced to constantly pass everything through clouds of variables, exactly as in above mentioned example (obviously, in this particular case we could have used constants, it's not the point here).

What is a bit funny, although absolutely understandable and even required is that there's no such problem when using functions:

select v1, v2, v3, [dbo].[StupidFunction] ( v1*v2, v3, default ) as rvStupidFunction
from (select 2 as v1, 2 as v2, 2 as v3) as dataset

v1          v2          v3          rvStupidFunction
----------- ----------- ----------- ----------------------
2           2           2           8

This just has got to work as this is SQL and SQL itself doesn't have such limitation. Is this because of this awkward T-SQL script-like syntax?

This just doesn't work here:

declare @rv float
declare @v1 float = 2
declare @v2 float = 2
declare @v3 float = 2
EXECUTE @rv = [dbo].[StupidFunction] @v1*@v2, @v3
print @rv

To make it work you have to create another variable and set its value, like so:

declare @rv float
declare @v1 float = 2
declare @v2 float = 2
declare @v3 float = 2
declare @v1calc float=@v1*@v2  -- at least this can be a one liner...
EXECUTE @rv = [dbo].[StupidFunction] @v1calc, @v3
print @rv

It's just a non existing problem in Oracle:

declare
    v1 number := 2;
    v2 number := 2;
    v3 number := 2;
begin
    dbms_output.put_line ( StupidFunction ( v1*v2, v3 ) );
end;
/

Did I mention I also dislike prefixing variables with at sign (@)? It helps, however, a bit with the clarity as you know right away what is a variable and what's not. Still, not a fan. Do C/C++, Java, C#, PL/SQL and myriads of other languages need any such prefixing? That's what I thought :)