Convert CURSOR into SELECT - Send parent table value to joined table returning function as a paramet-CodePudding

Currently this cursor takes lots of times to execute. And I believe that the best solution is converting the cursor into SELECT statement. I have implemented a bit of select statement but unable to send parent table's value to second table returning function as a parameter. Do you have any idea how I can get the exact same result with SELECT statement?

Here's the original CURSOR implementation

declare @orgusers table (UserID int, PRIMARY KEY (UserID))
                delete from @orgusers
                declare curOrgs CURSOR FAST_FORWARD FOR
                    select distinct ('~'   qlu.MemberValue   '~') as curOrgs                    
                    FROM [gl].VTN_QualificationMemberLU qlu 
                    where qlu.MemberType = 45 and qlu.QualificationID = 1111
                open curOrgs
                FETCH NEXT FROM curOrgs INTO @org
                while @@FETCH_STATUS = 0
                begin
                    insert into @orgusers(UserID) 
                    SELECT distinct u.UserID
                    FROM [gl].[GetStudentsByOUPath_BeforeCutoff](@nCompanyid, @org, @dCutOffDate) as u
                    left join @orgusers ou on ou.UserID = u.UserID
                    where ou.UserID is null
                    fetch next from curOrgs INTO @org
                end 
                close curOrgs
                deallocate curOrgs

I'm looking for a way that how can I convert this CURSOR into SELECT statement to get the same IDs in @orgusers TABLE.

Scenario to repro.

qlu.MemberValue returns data from the table called VTN_QualificationMemberLU something like - 2343-2987-2783-2873

So based on MemberValue function called GetStudentsByOUPath_BeforeCutoff returns a table based on some logic containing something like 2343 2783 2873

and we put this Ids into @orgUsers table and return as a result of the function.

So based on requirement - VTN_QualificationMemberLU table has 10 data. And we need to return 30 ID back by filtering using the function called GetStudentsByOUPath_BeforeCutoff. For each row of the VTN_Qual.... we need to return 3 Ids as a table result.

Here's the test scripts to repro.

CREATE TABLE VTN_QualificationMemberLU (
    MemberValue  nvarchar(max) NOT NULL
)


INSERT INTO VTN_QualificationMemberLU (MemberValue) VALUES 
('2385-3772-3764-2918'),
('2385-3772-3764-2918'),
('2382-1237-1203-2367'),
('2123-1238-8123-1232'),
('1238-1222-9982-7129'),
('2385-3772-3764-2918'),
('5645-1232-1203-1232'),
('6675-1238-5655-1232'),
('1238-1233-1232-1231')



CREATE FUNCTION GetStudentsByOUPath_BeforeCutoff  (
    @userIds NVARCHAR(MAX)
)
RETURNS TABLE
AS
RETURN 
    SELECT value  
    FROM STRING_SPLIT(@userIds, '-')  
    WHERE RTRIM(value) <> '';

So for each row of the VTN_Quali... table we need to call function and need to collect that table result of the function.

Thanks all in advance.

CodePudding user response：

Here's the test scripts to repro.

I ignore the description before this since we do not ahve the DDL DML to reprodcue the full scenario so let's focus on your sample table and data

-- DDL DML
CREATE TABLE VTN_QualificationMemberLU (
    MemberValue  nvarchar(max) NOT NULL
)
GO

INSERT INTO VTN_QualificationMemberLU (MemberValue) VALUES 
('2385-3772-3764-2918'),
('2385-3772-3764-2918'),
('2382-1237-1203-2367'),
('2123-1238-8123-1232'),
('1238-1222-9982-7129'),
('2385-3772-3764-2918'),
('5645-1232-1203-1232'),
('6675-1238-5655-1232'),
('1238-1233-1232-1231')
GO

CREATE FUNCTION GetStudentsByOUPath_BeforeCutoff  (
    @userIds NVARCHAR(MAX)
)
RETURNS TABLE
AS
RETURN 
    SELECT value  
    FROM STRING_SPLIT(@userIds, '-')  
    WHERE RTRIM(value) <> '';
GO

In order to execute the table's function on all the rows in existing table we can use CROSS APPLY

For example:

SELECT t1.MemberValue,t2.[value]
from VTN_QualificationMemberLU t1
CROSS APPLY GetStudentsByOUPath_BeforeCutoff (MemberValue) as t2

Obviously, if you need only the clean values from the function then you can return only the column t2.[value]

CodePudding user response：

Well, it's really not possible to for me to test this because there's so many discrepancies (missing variables, columns, etc.) between your question code and your example code & data, but here is what it probably should look like. I have just commented out the cursor code in-place so that you can better see how to do the transformation of the code from cursor to set-based:

declare @orgusers table (UserID int, PRIMARY KEY (UserID))
delete from @orgusers

--declare curOrgs CURSOR FAST_FORWARD FOR
;WITH cte AS
(   select distinct ('~'   qlu.MemberValue   '~') as curOrgs                    
    FROM [gl].VTN_QualificationMemberLU qlu 
    where qlu.MemberType = 45 and qlu.QualificationID = 1111
)
--open curOrgs
--FETCH NEXT FROM curOrgs INTO @org
--while @@FETCH_STATUS = 0
--begin
    insert into @orgusers(UserID) 
    SELECT distinct u.UserID
    FROM cte AS c
    CROSS APPLY [gl].[GetStudentsByOUPath_BeforeCutoff](@nCompanyid, c.curOrgs, @dCutOffDate) as u
    left join @orgusers ou on ou.UserID = u.UserID
    --where ou.UserID is null
    --fetch next from curOrgs INTO @org
--end 
--close curOrgs
--deallocate curOrgs

I think that you can probably get rid of the distinct in the CTE (you shouldn't need two DISTINCT's and the later one will do the work of both).