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