5

There is function Getfunctionname(userid, startdate, enddate) to return a table

My question is can I pass a variable with multiple values?

i.e. 

getfunctionname(@userid, startdate, enddate)

Where the value of variable @userid is like 

1
2
3
4
5

(actually using split function splitting the values from being 1,2,3,4,5 )

If I can please let me know

sqluser
  • 5,145
  • 7
  • 32
  • 48
Aswin
  • 83
  • 1
  • 2
  • 10
  • http://stackoverflow.com/questions/4624398/passing-multiple-values-for-one-sql-parameter – AcAnanth Nov 03 '15 at 05:25
  • Possible duplicate of [Passing a varchar full of comma delimited values to a SQL Server IN function](http://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function) – Mahesh Nov 03 '15 at 05:27

2 Answers2

2

One way of doing that which I prefer is to make a new user-defined table data type. 

CREATE TYPE [dbo].[IdList] AS TABLE(
    [Id] [int] NULL
)

Then you can use that data type as one of the parameters

CREATE FUNCTION Getfunctionname
(   
    @UserIDs dbo.IdList READONLY,
    @startdate INT,
    @endtdate INT
     )
RETURNS @ReturnTable TABLE                                        
   (                                        
     -- ReturnTable
   )
AS
BEGIN
  -- Query    
RETURN

END
sqluser
  • 5,145
  • 7
  • 32
  • 48
1

Use the concept of CSV

CREATE FUNCTION [dbo].[uspGetNumbers]
    userid,startdate,enddate // define your paramters the way you want
AS
BEGIN
// your code
JOIN dbo.fnSplit(@UserIDs, ',') 
END
GO

Example function:

SELECT [dbo].[uspGetNumbers] '1,2,3,4,5', '', ''
MusicLovingIndianGirl
  • 5,743
  • 8
  • 32
  • 65