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,502
  • 7
  • 36
  • 50
Aswin
  • 93
  • 1
  • 2
  • 11
  • 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

3 Answers3

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,502
  • 7
  • 36
  • 50
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,909
  • 9
  • 34
  • 65
0

I just ran into this, and I used the CROSS APPLY solution from this post: SQL Server: run function for each row based on the provided row value

To use CROSS APPLY, you would need to first select your values, and then CROSS APPLY. I have not used the split function before, so I don't have the exact syntax, but if you use it something like:

select @userid, F1.* from split(1,2,3,4,5), CROSS APPLY getfunctionname(@userid, startdate, enddate) F1

jenstreetman
  • 353
  • 3
  • 8