CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2012
Aswin
Aswin

Reputation: 93

Passing multiple values to a parameter of a function in SQL

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

Upvotes: 5

Views: 32294

Answers (3)

jenstreetman
jenstreetman

Reputation: 353

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

Upvotes: 0

sqluser
sqluser

Reputation: 5672

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

Upvotes: 2

MusicLovingIndianGirl
MusicLovingIndianGirl

Reputation: 5947

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', '', ''

Upvotes: 1

Related Questions