Multiple values in a single parameter of a scalar function

Question

Is there a way to input multiple values in a single parameter of a scalar-valued function in SQL Server 2008 R2 and have it filter data by that parameter using both values?

For example I would like to do the following

    SET @Salesperson='BILL' OR 'MOSES'

    SELECT Sum(SalesDollars)
    FROM Invoices
    WHERE Invoices.Salesperson = @Salesperson

I attempted to use the following as the WHERE clause, but this didnt work either.

    SET @Salesperson='BILL','MOSES'

    SELECT Sum(SalesDollars)
    FROM Invoices
    WHERE Invoices.Salesperson IN (@Salesperson)

Would it be easier if i were dealing with integers as opposed to varchar values?

Any help would be absolutely appreciated!

vittore · Accepted Answer · 2016-04-20T14:05:11.273

You need to use table-valued parameters. Look it up on technet or msdn

Best part of it that your table-valued parameters can have multiple columns.

Note however that you have to define TVP parameter as readonly. So if you want to return similar set from your function you will need to create another variable inside your function.

Example:

  CREATE TYPE Names AS TABLE 
  ( Name VARCHAR(50));
  GO

  /* Create a procedure to receive data for the table-valued parameter. */
  CREATE PROCEDURE dbo.mySP
      @n Names READONLY
      AS 
      SELECT Sum(SalesDollars)
      FROM 
      WHERE Invoices.Salesperson in (select Name from @n)
  GO

  CREATE FUNCTION dbo.myFun(@n Names READONLY) returns int
      AS
      SELECT Sum(SalesDollars)
      FROM 
      WHERE Invoices.Salesperson in (select Name from @n)
  GO

  /* Declare a variable that references the type. */
  DECLARE @names AS Names;

  /* Add data to the table variable. */
  INSERT INTO @names (Name)
  VALUES ('BILL'),('MOSES')

  -- using stored procedure with TVP
  EXEC dbo.mySP @names

  -- using function with TVP
  select dbo.myFun(@names)
  GO

yeah, my bad. rushed to answer. – Trojan.ZBOT Dec 11 '13 at 21:18 — Trojan.ZBOT, Dec 11 '13 at 21:18

score 0 · Answer 2 · edited May 23 '17 at 12:07

0

This could be done this way:

SET @Salesperson='BILL,MOSES'

SELECT *
FROM YourTable
WHERE Invoices.Salesperson IN (SELECT * FROM dbo.split(@Salesperson,','))

This is how you split the values.

edited May 23 '17 at 12:07

Community

1
1

answered Dec 11 '13 at 21:11

Yosi Dahari

6,794
5
24
44

This looks fairly simple and easy to use, the only problem i am having is that the dbo.split is in invalid object name. Is this a function you created yourself? – Harif87 Dec 11 '13 at 21:50
After understanding vittore's answer, i now understand what you mean - i actually named my table valued function the same way so i can use it as you recommend. Toda raba Yosi – Harif87 Dec 11 '13 at 22:38

Mark Brown · Answer 3 · 2013-12-11T22:03:37.740

I would typically do this using a user defined table type: SQL Fiddle Example.

CREATE TYPE <schema>.SalespersonList AS TABLE
(
   Name varchar(32)
)

You may have to grant execute permissions on the type:

GRANT EXECUTE ON TYPE::<schema>.SalespersonList TO <user>

Then you can create a function to use it:

CREATE FUNCTION <schema>.fnGetTotalSales
(
    @nameList <schema>.SalespersonList READONLY
)
RETURNS INT
AS
BEGIN 
    DECLARE @ret INT

    SELECT @ret = Sum(SalesDollars)
    FROM Invoices i
    INNER JOIN @nameList nl ON nl.Name = i.Salesperson

    RETURN @ret
END

Then you would just insert your list into the type and call the function:

DECLARE @salesPersonList <schema>.SalespersonList 

INSERT INTO @salesPersonList (Name)
SELECT 'Bill'
UNION 
SELECT 'Moses'

SELECT <schema>.fnGetTotalSales(@salesPersonList)

Multiple values in a single parameter of a scalar function

3 Answers3