Loop thru List of IDs

I am trying to figure out how to change my query to accept multiple values for the @agentid instead of just one and then loop through the query for each agentid. I guess I am hung-up on how to declare that and then which loop to use. Any help would be greatly appreciated!

declare
@Startdate datetime,
@Enddate datetime,
@agentid varchar(100)

set @startdate = '4/17/2023'
set @enddate = '8/07/2023'
set @agentid = '1024971' --would like to change to ('12345','6789','98763','246810') as example and then loop through code below for each id in the list.

--AS
--BEGIN
SET NOCOUNT ON

/* 
DECLARE @YearStart AS datetime =  '2019-11-20 00:00:00', @Startdate AS datetime = '2019-11-17 00:00:00', @Enddate AS datetime =  '2019-11-25 00:00:00'
   */
DELETE FROM tblGenesys
FROM            tblGenesys INNER JOIN
                         tblUsers2 AS u ON tblGenesys.AgentID = u.GenesysLogon AND tblGenesys.AgentSummaryDate = u.ExtractDate
WHERE ([AgentSummaryDate] >= @Startdate and [AgentSummaryDate] < DATEADD(dd,1,@Enddate)) AND [GenesysGroupName] = 'US' and u.employeeid = @agentid

INSERT INTO [dbo].[tblGenesys]
( 
[Username], [AgentSummaryDate], [GenesysGroupName], [AgentNameRaw], [AgentIDRaw], [LastName], [FirstName], [AgentID],
AgentIDSuffix, [Account], [AccountGroup], [AccountOrganization], [Supervisor], [SVP], [JobTitle], [Client_Paid],
 [TotalDuration], [InboundCount], [InboundTime], [OutboundCount], [OutboundTime], [InternalCount], [InternalTime],
  [HoldTime], [ACWTime], [WaitTime], [DialTime], [BreakTime], FaxEmailTime, [LeadTime], [LunchTime], [Meet_TrainTime],
   [OtherTime], [ProjectTime], [SystemIssueTime], [NotReadyTime], [HRTime], [ManualWorkTime], [OtherChannelTime], [Transferred], 
   [WeekOf], [DayOfWeek], [MonthOf], GenesysLogon
)
SELECT 
g.[Username], [AgentSummaryDate], [GroupName], [AgentNameRaw], [AgentIDRaw], g.[LastName], g.[FirstName], [AgentID],
AgentIDSuffix, g.[Account], g.[AccountGroup], g.[AccountOrganization], g.[Supervisor], g.[SVP], g.[JobTitle], [Client_Paid],
 [TotalDuration], [InboundCount], [InboundTime], [OutboundCount], [OutboundTime], [InternalCount], [InternalTime],
  [HoldTime], [ACWTime], [WaitTime], [DialTime], [BreakTime], FaxEmailTime, [LeadTime], [LunchTime], [Meet_TrainTime],
   [OtherTime], [ProjectTime], [SystemIssueTime], [NotReadyTime], [HRTime], [ManualWorkTime], [OtherChannelTime], [TranMake], 
   DATEADD(DD, 2 - DATEPART(DW, DATEADD(DD, 0, agentsummarydate)), DATEADD(DD, 0, agentsummarydate)) as WeekOf, LEFT(DATENAME(dw, CAST(agentsummarydate AS DATE)), 3) 
                      AS DayOfWeek, LEFT(DATENAME(mm, CAST(agentsummarydate AS DATE)), 3) + '-' + RIGHT(DATENAME(YY, CAST(agentsummarydate AS DATE)), 2)  AS [MonthOf], g.AgentID
FROM [uv_Genesys_AgentSummaryGroup_CST] as g inner join
tblusers2 as u on g.AgentID = u.GenesysLogon and g.AgentSummaryDate = u.ExtractDate
where [AgentSummaryDate] >= @Startdate and [AgentSummaryDate] < DATEADD(dd,1,@Enddate) and u.EmployeeID = @agentid
ORDER BY [AgentSummaryDate], [LastName] , [FirstName]


-- Select for presentation in report
SELECT count(*) as [RecordsInsertedTable], min(D.[AgentSummaryDate]) as [FirstDate] ,min(D.[WeekOf]) as [FirstWeekOf],max(D.[AgentSummaryDate]) as [LastDate]
,max(D.[WeekOf]) as [LastWeekOf],min(D.[CreateDate]) as [FirstDBCreateDate],max(D.[CreateDate]) as [LastDBCreateDate]
FROM [dbo].[tblGenesys] as D with(nolock) inner join
tblUsers2 as u on D.AgentID = u.GenesysLogon and D.AgentSummaryDate = u.ExtractDate
WHERE D.[AgentSummaryDate] >= @Startdate And  D.[AgentSummaryDate] < DATEADD(dd,1,@Enddate) AND D.[GenesysGroupName] = 'US' and u.EmployeeID = @agentid

Turn @agentid into a temp table.

DROP TABLE IF EXISTS #AgentID

SELECT
	A.AgentID
INTO #AgentID
FROM
	(
	VALUES
		 ('1234')
		,('2341')
		,('3412')
		,('4123')
	) A(AgentID)

Then, do an Inner Join at any table that has an AgentID field to join to and limit your query pull.

FROM [uv_Genesys_AgentSummaryGroup_CST] AS G 
INNER JOIN tblusers2 AS U
	ON	G.AgentID = U.GenesysLogon 
	AND G.AgentSummaryDate = U.ExtractDate
INNER JOIN #AgentID AS AI 
	ON	G.AgentID = AI.AgentID

Lastly, remove any AND U.AgentID = @agentid from all of your where statements.

The format requires the following:

• From statement will have an open & close parentheses. From (...)
• Table must be named, and have the column name identified inside parenthesis. From (...) AS A(ColName)
• Inside the From parenthesis, you will use VALUES (val1),(val2),...(valn)

To make formatting easier, list your ID's in Excel using column A. Then run the formula =CONCAT("('", TEXTJOIN("'),('", TRUE(), A:A), "')"). This will format your agent ids into the required syntax for this method.

Limitations:
You will be limited to approximately 1000 ids or less. So only useful for small to medium scale needs.

something like this

 CREATE FUNCTION [dbo].[fn_Split_String]
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
WITH E1(N)        AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
E2(N)        AS (SELECT 1 FROM E1 a, E1 b),
E4(N)        AS (SELECT 1 FROM E2 a, E2 b),
E42(N)       AS (SELECT 1 FROM E4 a, E2 b),
cteTally(N)  AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1))) 
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
FROM cteStart s;
declare
@Startdate datetime,
@Enddate datetime,
@agentid varchar(100)  ='xx,yy,ff,dd',	
@x	varchar(50)	= 1
declare @agentid_list table (id varchar(50) , RN varchar(50))
insert into @agentid_list
SELECT *  ,ROW_NUMBER()over(order by (select 1) )  RN
from [dbo].[fn_Split_String](@agentid,',')
WHILE (SELECT max(RN) FROM @agentid_list) >= @x
BEGIN
SELECT * FROM @agentid_list	where rn = @x 
--u.EmployeeID = (SELECT id FROM @agentid_list	where rn = @x)
set @x = @x +1
END