Wednesday, July 6, 2011

Product Suggestion: Allow parameter-name = expression in function calls

Both the CALL statement and the FROM clause allow the following syntax for a procedure call (although that fact is not apparent from the latter Help topic:


procedure-name ( parameter-name = expression , ... )

That might be a legacy holdover from The Time Before SQL Anywhere, but like beer is a holdover from The Time Before The Industrial Revolution, it is a good thing. Consider a call to sa_rowgenerator... suppose the author wishes to make sure the reader understands exactly what the call is doing without appealing to the docs...

SELECT * FROM sa_rowgenerator ( 1, 7, 1 )

Go ahead, without looking it up, tell me what that does.

... I'll wait.

... waiting.



Here's the answer:

SELECT * FROM sa_rowgenerator ( rstart = 1, rend = 7, rstep = 1 )

row_num
1
2
3
4
5
6
7

The rules for expressions should be extended to allow the same thing, instead of throwing Syntax error near '=' on line 1:

CREATE FUNCTION function_name (
parameter_1 INTEGER,
parameter_2 INTEGER )
RETURNS INTEGER
BEGIN
RETURN parameter_1 + parameter_2;
END;

SELECT function_name ( parameter_1 = 2, parameter_2 = 2 );

Of course, authors who don't care about the reader (and they are in the vast majority) would not need to use this syntax.

(Yes, the ambiguity was intended... both readers, and authors who don't care, vastly outnumber authors who care... I see the code every day.)

Does it already work in SQL Server?


What do you think?

Let's see... of course not!

1> CREATE FUNCTION function_name (
2> @parameter_1 INTEGER,
3> @parameter_2 INTEGER )
4> RETURNS INTEGER
5> BEGIN
6> RETURN @parameter_1 + @parameter_2;
7> END
8> GO
CREATE FUNCTION function_name (
@parameter_1 INTEGER,
@parameter_2 INTEGER )
RETURNS INTEGER
BEGIN
RETURN @parameter_1 + @parameter_2;
END
1>
2> SELECT dbo.function_name ( @parameter_1 = 2, @parameter_2 = 2 )
3> GO
Msg 137, Level 15, State 2, Server ENVY, Line 2
Must declare the scalar variable "@parameter_1".

Just to prove it wasn't a problem with the CREATE FUNCTION...

1> SELECT dbo.function_name ( 2, 2 )
2> GO
SELECT dbo.function_name ( 2, 2 )

-----------
4

(1 row affected)


4 comments:

Unknown said...

This is a great idea.

http://dcx.sybase.com/index.html#1001/en/dbrfen10/rf-call-statement.html

Perhaps you can take a look at the comment section ;-)

It would also be usefull for functions/procedures that have some default values defined. Instead of

call xy( NULL, NULL, NULL, 4)

you could write

call xy( PARAMETER_4 = 4 )

Thomas

Breck Carter said...

@Thomas: Laura has replied to you on DCX.

Unknown said...

I know. But the last comment from kwriter is

"While this statement can be used to invoke a function, this behavior is deprecated."

So I think we will not see this feature in the future.

Breck Carter said...

@Thomas: kwriter's comment on the DCX topic http://dcx.sybase.com/index.html#1001/en/dbrfen10/rf-call-statement.html is referring to the deprecated feature of CALLing a function. That's fine, nobody should CALL a function, and it has nothing to do with whether or not "parameter-name = expression" can be passed as an argument to a function; currently, it cannot, and it should be allowed.