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