Discussion: SQL Function - Unknown parameters count

Question

I wanted to share with you the following idea.

I very love the "SQL Functions". But the big deficiency of them, That you need to explain all the parameters.

So if I have a function. and I need to add more parameter. I need to find all his callings, and to add the new parameter.

I thought to decide that any function, be contain, Let's say, 10 parameters with defaults to all unused parameter.

For that matterת I have the function "MyFN". She will be shown like that:

create function MyFN(@I int, @S nvarchar(50), @D datetime, @P4 sql_variant = null, @P5 sql_variant = null, @P6 sql_variant = null, @P7 sql_variant = null, @P8 sql_variant = null, @P9 sql_variant = null, @P10 sql_variant = null)
returns int
as
begin
      return 1
end

We will call to her like that:

select dbo.MyFN(1,'xxx',getdate(),default,default,default,default,default,default,default) -- => 1

Now we need to add a new parameter - real.

Now the function will be like that:

alter function MyFN(@I int, @S nvarchar(50), @D datetime, @R real = 0, @P5 sql_variant = null, @P6 sql_variant = null, @P7 sql_variant = null, @P8 sql_variant = null, @P9 sql_variant = null, @P10 sql_variant = null)
returns int
as
begin
      if @R > 0
            return 2
      return 1
end

And we will call her:

select dbo.MyFN(1,'xxx',getdate(),0.5,default,default,default,default,default,default) -- => 2

And we can still call her, like the previous method, and to get the same answer.

I would love to hear your thoughts on the matter!

And how much parameters you thing that need to set.

Thank you very much!

YAGNI? Besides default doesn't work that way. Just leave the arguments out of your call. If you add a new argument to a function the old calls will still work. Many programmers will argue that defaults are a bad idea in general. — shawnt00
– shawnt00, Commented Jun 2, 2016 at 14:34
I would just avoid the scalar functions in the first place. They are notoriously poor performers. An inline table valued function is faster and far more flexible. That being said I have to say I don't understand what your question is here. — Sean Lange
– Sean Lange, Commented Jun 2, 2016 at 14:36
@SeanLange I believe the question is: Should I create all my functions with 10 arguments and pad the calls with default parameters so I can add new (functional) arguments in the future? Obviously this ignores issues with types completely. — shawnt00
– shawnt00, Commented Jun 2, 2016 at 14:39
Oh good grief....if that is the question this is an abomination of how you build anything with programming at any level. You don' add parameters as placeholders in case you might need them someday. That is just awful. — Sean Lange
– Sean Lange, Commented Jun 2, 2016 at 14:41
I guess it didn't ignore the type issue; I hadn't noticed they were specified as sql_variant. @inon, issues with variant types and scalar functions aside (and whether this would work as you expected) this is really not a good idea. Is there a problem you were trying to solve with this? — shawnt00
– shawnt00, Commented Jun 2, 2016 at 14:47

Chris Steele · Accepted Answer · 2016-06-02 15:04:15Z

3

No, don't do that. In an attempt to simplify possible later modifications, you've created something that is less readible. Plus, who knows what types you'll need for later parameters.

An alternative would be to just create a new version of the function with the extra parameter later, and alter the old one to just call the new.

answered Jun 2, 2016 at 15:04

Chris Steele

1,3811 gold badge9 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

inon Over a year ago

Thank you for your answer, I think that your way it realy better... :)

Mike · Accepted Answer · 2016-06-02 15:12:26Z

0

Why not use an xml or some other text structure type?

CREATE FUNCTION [dbo].TEST ( @String XML )
RETURNS INT
AS
BEGIN
DECLARE @Z AS INT 
SELECT  @z=T.c.value('value[1]', 'int')
FROM @String.nodes('/Root') T(c)


RETURN @Z

END 
GO
SELECT DBO.TEST('<Root><value>5</value></Root>')

May not be super fast but it is extremely flexable

answered Jun 2, 2016 at 15:12

Mike

6,1009 gold badges60 silver badges94 bronze badges

Collectives™ on Stack Overflow

Discussion: SQL Function - Unknown parameters count

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related