3

In essence, I'd like the ability to create a scalar function which accepts a variable number of parameters and concatenates them together to return a single VARCHAR. In other words, I want the ability to create a fold over an uncertain number of variables and return the result of the fold as a VARCHAR, similar to .Aggregate in C# or Concatenate in Common Lisp.

My (procedural) pseudo code for such a function is as follows:

  1. define a VARCHAR variable
  2. foreach non-null parameter convert it to a VARCHAR and add it to the VARCHAR variable
  3. return the VARCHAR variable as the result of the function

Is there an idiomatic way to do something like this in MS-SQL? Does MS-SQL Server have anything similar to the C# params/Common Lisp &rest keyword?

-- EDIT --

Is it possible to do something similar to this without using table-valued parameters, so that a call to the function could look like:

MY_SCALAR_FUNC('A', NULL, 'C', 1)

instead of having to go through the rigmarole of setting up and inserting into a new temporary table each time the function is called?

Ben H
  • 494
  • 3
  • 10

1 Answers1

2

For a set of items, you could consider passing a table of values to your function?

Pass table as parameter into sql server UDF

See also http://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx

To answer your question directly, no, there is no equivalent to the params keyword. The approach I'd use is the one above - Create a user-defined table type, populate that one row per value, and pass that to your scalar function to operate on.

EDIT: If you want to avoid table parameters, and are on SQL 2012, look at the CONCAT function:

http://technet.microsoft.com/en-us/library/hh231515.aspx

CONCAT ( string_value1, string_value2 [, string_valueN ] )

This is only for the built-in CONCAT function, you couldn't roll-your-own function with "params" style declaration.

Community
  • 1
  • 1
Meff
  • 5,889
  • 27
  • 36
  • Sorry, I should have been more clear. I was specifically looking to avoid table-valued parameters, I'll update the question to make it clearer. – Ben H Aug 30 '13 at 10:01
  • @BenH I edited in an option, only applicable for string concatenation and also only on SQL 2012 if it helps? – Meff Aug 30 '13 at 10:05
  • That does help, thank you Meff. It makes me wonder how they've achieved the implementation of that function though. We're not using 2012 for this particular project, but we are on others and I was unaware of that function, so thanks. – Ben H Aug 30 '13 at 10:06
  • After more searching it looks like yours is the correct answer. Unfortunately, MSFT doesn't seem to have provided the params implementation used in 2012's CONCAT to the rest of us. I'm marking you as the correct answer. – Ben H Aug 30 '13 at 10:12
  • I am coming after 8 years... just curious... how is the built-in "COALESCE" function created? it can accepts infinite params – zeroflaw Jun 17 '21 at 01:49
  • 1
    @zeroflaw There are multiple variadic functions in T-SQL (`coalesce`, `string_agg` etc), but there doesn't seem to be any way of creating your own in T-SQL, which is a shame. You can use C# to create functions for T-SQL, perhaps it's possible there; I haven't looked into it. – Ben H Jan 25 '23 at 11:30