T-SQL: use scalar functions as stored procedure parameters - by Jamie Thomson

Status : 


Sign in
to vote
ID 352110 Comments
Status Active Workarounds
Type Suggestion Repros 16
Opened 6/20/2008 2:51:30 AM
Duplicates 651265 Access Restriction Public


SQL 2008 has given us some great enhancements in the T-SQL language that basically mean we have to wtite less code. In the same spirit I propose that you allow us to use scalar functions as stored procedure parameters.
Sign in to post a comment.
Posted by F00MaticPrinz on 12/31/2015 at 8:56 AM
Here is an example - let's say we have a generic logger proc in a database called "utility" and in schema called "logger".

And, I want to easily log from my work database, called "foo":

Ideally, I should be able to do this: utility.logger.info db_id(), @@procid, 'my message'

Oh, but, no!

Microsoft doesn't let its own function, db_id(), be passed to the proc. Of course, @@procid works, but it is database specific, making it useless if the logger proc is in a different database than where the log request comes from. (For some reason they don't have a @@dbid, why? You've got @@procid, @@servername, but no @@dbid?!)

So, we're screwed with this restriction - a restriction that makes no sense.
Posted by MattP88 on 4/30/2015 at 3:59 PM
been waiting for this for a long time.
Why is this ok
select * from mytable where mydatecol > getdate()
But this is not ok
EXEC mySelStoredProc getdate()
Posted by jyao on 11/8/2012 at 12:42 PM
Now even in sql 2012, this suggestion is still not accepted. Is this that hard that it cannot be solved since sql 2005? Microsoft, please do not ignore the small things as you never know whether this teeny tiny issue will lose a potential user from switching Oracle / DB2 to SQL server.
Posted by CheetahAfoot on 3/15/2011 at 2:44 AM
It would be nice to have ability to use not only scalar functions but expression when call procedure.
exec myProc 1, ('prefix:'+@s), (select count(1) from tableA)

And possible to pass table function result if it return table with format of the input table parameter.
Posted by RobertMiller on 3/29/2009 at 11:32 PM
It often seems like T-SQL has been missing the "Love" that other aspects of the Microsoft development environment have been receiving -- Heck, even "System Administrators" have PowerShell.

Not until SQL 2008 was I able to actually declare and set the value for a local variable instead of doing the two-step dance. Jumping back and forth between C#, ASP.Net, and T-SQL makes T-SQL seem just arcane.

Having the ability to declare the result of this scalar function call is to be used as this parameter for this Stored Procedure would eliminate another layer of coding gotcha, (Did I call the function and assign the return value to a local variable and then pass that local variable to a stored procedure instead of pass the result of this scalar function as this parameter to this stored procedure -- Simpler and clearer as to the intent).
Posted by Olaf Pietsch DETECON on 3/28/2009 at 8:56 AM
I had discussions with colleagues why is it is not possible to use e. g. exec mySP newid(). I was asked where the error is. It is expected that this construct can be used.
Posted by Eric Wisdahl on 3/24/2009 at 10:10 AM
It often takes me by surprise that T-SQL does not support common features like accepting output from a query / function / routine or whatever. It seems to me that as long as the value coming in matches the expected data type it should work. These type of features make learning T-SQL easier for the community at large who have cut their teeth on other programming languages...
Posted by Jamie Thomson on 6/23/2008 at 11:17 AM
You got it! I just want to pass in a value without writing 2 lines of code!

Thanks for the great reply. As a SQL MVP I'm first to criticise the SQL team when their responses to feedback are not up to scratch. Yours is the second I have received from the SQL engine team today and both have been excellent.

Understood that its too late for SQL10. I want this on the slate for SQL11.

Posted by Microsoft on 6/23/2008 at 10:59 AM
Hi Jaimie,

Agreed! More generally, wherever TSQL expects, say, and integer value, it should accept a literal, a variable, or the result of a function whose return type is integer. It just makes the language more regular ("orthogonal") and easier to learn/use.

That said, it's too late for this feature in the Katmai release, but I'll add it to our TODO list.

[In passing, I'm reading your request to mean what I said above. ie, you still want to pass a value to a procedure - not a pointer-to-function - higher-order-functions. Right?]