SQL Server: user defined function parameter

Question

I was wondering if it is possible to create a user-defined function that can take a parameter of varying length (as well as other parameters).

Essentially I want to pass a parameter that then gets used in an IN statement. What I would like to do is have something like this:

CREATE FUNCTION ufnGetOpenNotificationValue 
    (@LegacyTypeID INT, 
     @MonthEnd DATE, 
     @YearStart DATE, 
     @YearEnd DATE)

Where @LegacyTypeID is a list of integers.

So then using the function might look something like this

SELECT RE_Auxiliary.dbo.ufnGetOpenNotificationValue
    ((1,2,3),'2014-07-31','2013-09-01','2014-08-31')

rather than

SELECT RE_Auxiliary.dbo.ufnGetOpenNotificationValue
        (1,'2014-07-31','2013-09-01','2014-08-31') + 
    RE_Auxiliary.dbo.ufnGetOpenNotificationValue
        (2,'2014-07-31','2013-09-01','2014-08-31') + 
    RE_Auxiliary.dbo.ufnGetOpenNotificationValue
        (3,'2014-07-31','2013-09-01','2014-08-31')

but if I try and pass multiple integers I get an error stating

Incorrect syntax near ','

Table Valued Parameters are about as close as you can get E.g. Pass table as parameter into sql server UDF - You cannot pass variadic arguments. — Alex K.
– Alex K., Commented Jul 21, 2016 at 14:32
Thanks @Alex K. - I think I'd stick to calling the function 3 times and summing them, if the list was longer I might have considered the table method you proposed — RickyTillson
– RickyTillson, Commented Jul 26, 2016 at 13:49

Community · Accepted Answer · 2017-05-23 11:51:29Z

As Alex K states you can't pass arrays as an input for a SQL function. You can pass table types (Pass table as parameter).

CREATE TYPE TableType 
AS TABLE (LegacyTypeID INT)

CREATE FUNCTION ufnGetOpenNotificationValue 
(@LegacyTypeID TableType, 
 @MonthEnd DATE, 
 @YearStart DATE, 
 @YearEnd DATE)
...
WHERE COLUMN_NAME IN (SELECT LegacyType FROM @LegacyTypeID)

You would then need to insert into a TableType variable before calling your function with that variable passed as a parameter

Another option would be to pass in your list in as a comma separated list of values. And then use a function (like this one) to use in your where clause

CREATE FUNCTION ufnGetOpenNotificationValue 
(@LegacyTypeID NVARCHAR(256), 
 @MonthEnd DATE, 
 @YearStart DATE, 
 @YearEnd DATE)
...
WHERE COLUMN_NAME in (SELECT val FROM dbo.f_split(@StringParameter, ','))

Which you could then call like this:

SELECT RE_Auxiliary.dbo.ufnGetOpenNotificationValue
('1,2,3','2014-07-31','2013-09-01','2014-08-31')

Thanks @Alex Fletcher, creating another function to split a comma seperated list seems like an elegent solution and not as awkward as creating the Table Valued Parameters that Alex K recommended

Collectives™ on Stack Overflow

SQL Server: user defined function parameter

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related