SQLTeam.com | Weblogs | Forums

Convert While loop to CTE

I m usiing below query to iterate the lists which is coming as an input parameter and then inserting rows via one by one basis into table variable.
Can you please help me to convert while loop into CTE

							DECLARE @tblAppID TABLE
                            (
                            ID INT IDENTITY(1,1),
                            app_ID INT
                            )

                            DECLARE @tblReqID TABLE     
                            (
                            ID INT IDENTITY(1,1),
                            req_ID INT
                            )

                            insert into @tblAppId
                            SELECT value FROM SplitToInt('6, 245', ',')

                            insert into @tblReqID
                            SELECT value FROM SplitToInt('100, 200', ',')

                            DECLARE @RowCount INT
                            SET @RowCount = (SELECT COUNT(app_ID) FROM @tblAppID)

                            DECLARE @I INT -- Initialize the iterator
                            SET @I = 1

                            -- table to store data on row by row basis
                            DECLARE @webAppTable TABLE
                                    (
                                    app_ID INT,
                                    req_ID INT,
                                    app_source VARCHAR(255),
                                    webAp_date datetime,
                                    webAp_ID INT,
                                    deny_date datetime,
                                    app_score FLOAT
                                    )

                            WHILE (@I <= @RowCount)
                            BEGIN

                            insert into @webAppTable 
                            SELECT top 1 w.app_ID, req_ID, app_source, webAp_date, webAp_ID, deny_date, app_score FROM web_applicants (NOLOCK) w
                            INNER JOIN build_app_conn (NOLOCK) b on w.app_ID = b.app_ID
                            WHERE w.app_ID = (select app_ID from @tblAppID where ID = @I) 
                            AND w.req_ID = (select req_ID from @tblReqID where ID = @I) 
                            AND b.building_ID = 6 order by webAp_date desc

                            SET @I = @I + 1
                            END

                            select * from @webAppTable

some of your tables are not provided, but can you make this your select that you are inserting from and remove the while loop

SELECT w.app_ID, req_ID, app_source, webAp_date, webAp_ID, deny_date, app_score
FROM web_applicants (NOLOCK) w
INNER JOIN build_app_conn (NOLOCK) b
on w.app_ID = b.app_ID
join @tblAppID ta
on w.app_id = ta.app_id
join @tblReqID tr
on w.req_id = tr.req_id
and tr.id = tr.id
AND b.building_ID = 6