CODEWITHSUNDEEP
sql-serverfunction
inon
inon

Reputation: 1772

Discussion: SQL Function - Unknown parameters count

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!

Upvotes: 0

Views: 363

Answers (2)

Mike
Mike

Reputation: 6050

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

Upvotes: 0

Chris Steele
Chris Steele

Reputation: 1381

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.

Upvotes: 3

Related Questions