CODEWITHSUNDEEP
sqlsql-server
Ben H
Ben H

Reputation: 494

Infinite optional parameters

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?

Upvotes: 3

Views: 1044

Answers (1)

Meff
Meff

Reputation: 5999

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.

Upvotes: 2

Related Questions