Interesting question about parameters.
My good friend and super-stealth ninja middle-tier, business objects, BLL, DAL, bad*ss programmer Craig Boland asked me a question the other day about an oddity with stored procedures and functions. Eh, whassat you say? What’s a super-stealth ninja middle-tier, business objects, BLL, DAL, bad*ss programmer? Well, he’s one of these smart guys that do the backend c# coding for web and fat clients, and gets to do all of the fun stuff, such as pattern programming (you’ve heard of patterns, such as Singleton, Observer, Bridge, Facad, Adapter…Hell there’s books on them and at last count there were slightly over 20 billion different programming patterns. j/k).
Anyway, Craig pointed out something when in comparing default parameters for stored procedures to default parameters in user-defined functions (UDFs) that I'd never paid much attention to, because I rarely use stored procedures without specifying all of the parameters. When would you? After all, in a process, SSIS package, or job, the syntax is hard-coded and so you would never change it, right? Well, Craig came to a situation where he might want to add parameters to a stored procedure while developing to maintain backward compatibility; in this instance, the new proc call would still work using the old proc call, but in development you would be able to add your additional parameter and use both the old call and new call method.
Here’s an example:
DROP PROC dbo.StoredProcedure
GO
CREATE PROC dbo.StoredProcedure @salary money
, @BonusPts decimal (10,2)
AS
SET NOCOUNT ON
SELECT @salary * @BonusPts
GO
EXEC dbo.StoredProcedure @salary=100000, @BonusPts=.2
GO
No problems here, TSQL 101. Here is his new procedure, the same one but in development to add a new feature – a “CoFactor” parameter:
DROP PROCEDURE dbo.StoredProcedure
GO
CREATE PROCEDURE dbo.StoredProcedure @salary money, @BonusPts decimal (10,2)
, @CoFactor decimal (10,2)
AS
SET NOCOUNT ON
SELECT @salary * @BonusPts * @CoFactor
GO
EXEC dbo.#CURRENT @salary=100000, @BonusPts=.2
GO
EXEC dbo.#CURRENT @salary=100000, @BonusPts=.2, @CoFactor = 1.1
Craig saw that he can rewrite stored procedure dbo.StoredProcedure while keeping the contract intact, which maintains the old call while developing the new business logic. Not a bad way to think about this, really. If you are developing, and want to maintain the current functionality for whatever reason (in his case he was interfacing XML in Visual Studio) you can, and then "flip the switch" so to speak when you want the new stuff available.
We're gold up to now. UDFs, however, follow a different set of rules. Let’s check them out:
CREATE FUNCTION dbo.UDF ( @salary money, @BonusPts decimal (10,2))
RETURNS DECIMAL
AS
BEGIN
RETURN (@salary * @BonusPts)
END
GO
SELECT dbo.UDF(100000,.2)
We have a problem, though, when we try the second example (stored procedure call, above):
DROP FUNCTION dbo.UDF
GO
CREATE FUNCTION dbo.UDF ( @salary money, @BonusPts decimal (10,2), @CoFactor decimal (10,2))
RETURNS DECIMAL
AS
BEGIN
RETURN (@salary * @BonusPts)
END
GO
SELECT dbo.UDF(100000,.2)
Msg 313, Level 16, State 2, Line 2
An insufficient number of arguments were supplied for the procedure or function dbo.UDF.
So his question…why wouldn’t they keep consistent this manner in which to fire off either one? Would it not make sense to allow defaults and optional parameters work in the same fashion so that development could take place on the old stuff while maintaining backward compatibility? Sorta seems like a good question to me, and right off I don’t have an answer for him for the difference.
Here's one that I couldn't explain, BTW, while I was dorking with this thing:
SELECT dbo.UDF(100000,.2, DEFAULT)
This works, but why? I never set a default anywhere in the function, either in the body or in the specification, such as this...WTH?
CREATE FUNCTION dbo.UDF ( @salary money, @BonusPts decimal (10,2), @CoFactor decimal (10,2)=1)
RETURNS DECIMAL
AS
BEGIN
RETURN (@salary * @BonusPts)
END
GO
So, I conclude nothing. Thanks for visiting, and have a super day!
Lee
---------------------------
I know what you're thinking - that's 5 minutes out of my life I'll never get back
Enjoy the post? Visit me at my blog site for research, learning, and a bit of humor.
