Harif87 Harif87 - 7 months ago 12
SQL Question

Multiple values in a single parameter of a scalar function

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!

Answer

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