Variable Paramters to Functions

[Jason]

From previous postings I have read it seems that I cannot create a
function that accepts a variable list of parameters. Seems that SQL
Server does not support this.

A few questions:
Is this true?

What is a workaround?

Then how does the function COALESCE do it?
Cut and pasted from online books:

Syntax
COALESCE ( expression [ ,...n ] )

Arguments
expression

Is an expression of any type.

n

Is a placeholder indicating that multiple expressions can be
specified. All expressions must be of the same type or must be
implicitly convertible to the same type.

[David Portas]

Re: Variable Paramters to Functions

> Is this true?
It's true that all parameters must be specified each time a User Defined
Function is called. But the parameter values can include NULL or DEFAULT
values.
[color=blue]
> What is a workaround?[/color]
That depends on your requirements. Put your parameter values in a table and
use a SELECT statement to calculate the result might be one solution -
possibly a more efficient solution than a UDF since UDF calls are relatively
slow. If you have a particular requirement in mind then give some details
and see if someone can help.
[color=blue]
> Then how does the function COALESCE do it?[/color]
COALESCE is a system function not a UDF. UDFs are created in TSQL using the
CREATE FUNCTION syntax. System functions are not.

--
David Portas
------------
Please reply only to the newsgroup
--

[Hayat]

Re: Variable Paramters to Functions

Yes, functions doesn't accept variable list of params.......th is is
true!

I faced this problem previously and i thought of two solutions:

1. use a temporary table, enter all params, read them from function
2. concat all params in a string separated by comma or some separator,
so some string manipulation in the function, extract all and use it.

-Hayat

https://www.mysticssoft.com

JayCallas@hotma il.com (Jason) wrote in message news:<f01a7c89. 0310030823.6718 2554@posting.go ogle.com>...[color=blue]
> From previous postings I have read it seems that I cannot create a
> function that accepts a variable list of parameters. Seems that SQL
> Server does not support this.
>
> A few questions:
> Is this true?
>
> What is a workaround?
>
> Then how does the function COALESCE do it?
> Cut and pasted from online books:
>
> Syntax
> COALESCE ( expression [ ,...n ] )
>
> Arguments
> expression
>
> Is an expression of any type.
>
> n
>
> Is a placeholder indicating that multiple expressions can be
> specified. All expressions must be of the same type or must be
> implicitly convertible to the same type.[/color]

[Erland Sommarskog]

Re: Variable Paramters to Functions

Jason (JayCallas@hotm ail.com) writes:[color=blue]
> From previous postings I have read it seems that I cannot create a
> function that accepts a variable list of parameters. Seems that SQL
> Server does not support this.[/color]

Depends on what you want to do, but if you want to submit a list of
scalar values to work on, you can put in a comma-separated string
and unpack into a table variable in the function. As for unpacking
that list, see here for a sample of such functions:
http://www.algonet.se/~sommar/arrays...html#iterative.



--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp