how to union some table in a while loop to a table

mhd sbt

hi to all i have a while loop like this

declare @Counter int
while(@Counter<1000)
begin
set @Counter = @Counter + 1
select * from mytable where [my where clause]
end

my where cluase change in each repeat. i want to union all result to a table and return one table as query result how can i do this ? performance is very important for me because while loop can be run for upper 15000 thanks for any help

Richard Deeming

mehdi.sabet wrote:

my where cluase change in each repeat.

Can you post your where clause? It can probably be replaced with a single query, which will give you much better performance.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

mhd sbt

hello thanks for reply my main code is:

ALTER PROCEDURE [dbo].[lfd__GetFullPersonnelDetails]
(
@PersonnelList nvarchar(MAX),
@WorkPeriodID int,
@WorkPeriodYear int
) AS
Declare @startDate SMALLDATETIME
Declare @endDate SMALLDATETIME
Declare @PersonnelBaseID INT

WHILE(LEN(@PersonnelList) > 0)
BEGIN
SET @PersonnelBaseID = SUBSTRING(@PersonnelList, 0, PATINDEX('%,%',@PersonnelList))
IF(@PersonnelBaseID != 0)
SET @PersonnelList = SUBSTRING(@PersonnelList, LEN(CAST (@PersonnelBaseID AS NVARCHAR) + ',') + 1, LEN(@PersonnelList))
ELSE
BEGIN
SET @PersonnelBaseID=CAST(@PersonnelList AS INT)
SET @PersonnelList = SUBSTRING(@PersonnelList, LEN(@PersonnelList + ',') + 1, LEN(@PersonnelList))
END

 set @startDate=(select top 1 \[date\] as startdate 
	from 
		lfd\_DailyStatistics
	 where 
		PersonnelBaseID = @PersonnelBaseID and \[Year\] = @WorkPeriodYear and  WorkingPeriodID = @WorkPeriodID 
	order by date asc)

 set @endDate=(select top 1 \[date\] as startdate 
	from 
		lfd\_DailyStatistics
	 where 
		PersonnelBaseID = @PersonnelBaseID and \[Year\] = @WorkPeriodYear and  WorkingPeriodID = @WorkPeriodID 
	order by date desc)

SELECT 
	dailyAbsences.\*,
	CAST(CASE WHEN calculationQueue.\[StartDate\] IS NULL THEN 0 ELSE 1 END AS bit) AS \[NeedsRecalculation\] 
FROM 
	lfd\_dailyAbsence dailyAbsences LEFT JOIN
	lfd\_CalculationQueue calculationQueue ON dailyAbsences.\[PersonnelBaseID\] = calculationQueue.\[PersonnelBaseID\] AND calculationQueue.\[StartDate\] <= @endDate 
WHERE 
	dailyAbsences.\[PersonnelBaseID\] = @PersonnelBaseID AND
	dailyAbsences.\[StartDate\] >= @startDate AND 
	dailyAbsences.\[EndDate\] <= DATEADD(day, 1, @endDate)
ORDER BY
	dailyAbsences.\[StartDate\]

END

start date and end date change in each repeat

Corporal Agarn

Why not create a temp table and insert the results there? When the while loop is done select * from your temp table.

mhd sbt

ok but what about performance ? if i done this and this loop execute ,for example , for 15000 repeat this is a optimal way? and how can use one temp table and insert all resualt to this temp table? thank you

GuyThiebaut

Little tip - get rid of the order by clause - as it is taking up unnecessary processing time. You can order the results once you have combined them.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Richard Deeming

OK, the first thing you need is a split function:

CREATE FUNCTION dbo.fn_Split
(
@Value varchar(max),
@Delimiter varchar(20) = ','
)
Returns TABLE
As Return
(
WITH cte (Idx, StartIndex, EndIndex) As
(
SELECT
1,
1,
CharIndex(@Delimiter, @Value)

    UNION ALL
    
    SELECT 
        1 + Idx, 
        CAST(1 + EndIndex As int), 
        CharIndex(@Delimiter, @Value, 1 + EndIndex)
    FROM 
        cte
    WHERE
        EndIndex > 0
)
SELECT
    Idx,
    SubString(@Value, StartIndex, CASE
        WHEN EndIndex > 0 THEN EndIndex - StartIndex
        ELSE LEN(@Value)
    END) As Value
FROM
    cte

);
GO

Then you can build a single query:

WITH ctePersonnelList (PersonnelBaseID) As
(
SELECT
CASE
WHEN Value Like '%[^0-9]%' THEN Null
ELSE CAST(Value As int)
END
FROM
dbo.fn_Split(@PersonnelList, ',')
WHERE
Value Not Like '%[^0-9]%'
),
cteDates (PersonnelBaseID, StartDate, EndDate) As
(
SELECT
L.PersonnelBaseID,
Min([date]),
Max([date])
FROM
ctePersonnelList As L
INNER JOIN lfd_DailyStatistics As S
ON L.PersonnelBaseID = S.PersonnelBaseID
WHERE
[Year] = @WorkPeriodYear
And
WorkingPeriodID = @WorkPeriodID
GROUP BY
L.PersonnelBaseID
)
SELECT
A.*,
CAST(CASE WHEN Q.StartDate IS NULL THEN 0 ELSE 1 END AS bit) As NeedsRecalculation
FROM
cteDates As D
INNER JOIN lfd_dailyAbsence As A
ON A.PersonnelBaseID = D.PersonnelBaseID
And A.StartDate >= D.StartDate
And A.EndDate <= DateAdd(day, 1, D.EndDate)
LEFT JOIN lfd_CalculationQueue As Q
ON A.PersonnelBaseID = Q.PersonnelBaseID
And Q.StartDate <= D.EndDate
ORDER BY
A.PersonnelBaseID,
A.StartDate
;

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Lost User

Looping in a database is never efficient. Working with tables is. Did that answer your question?

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

mhd sbt

thanks Richard this is a good solution