Hi
I'm hoping to get some feedback on my thought process in creating a stored procedure. I'm entirely self-taught (like so many others), so feedback like this is key to my development. Really appreciate any words of wisdom.
The goal is to create a stored procedure in SSMS that will take inputs from PowerApps and will use said inputs to create a CallList by (1) filtering tabMembershipList based on those that live in towns that are within X number of miles from StartTown and then (2) dividing the CallList by Y number of Staff Members and assigning a roughly equal number of calls to each Staff Member.
The business use starts when one of our associate companies submits a request for workers. Our dispatcher then fills out a form in PowerApps that has the following input (which are inserted into tabCreateCallList):
StartTownID int (FK from tabTown)
RadiusInMiles int
StaffMemberIDs varchar(100) (FK from tabStaffMember) eg: '4, 7, 12'
RequestForWorkersID int (FK from tabRequestForWorkers which has all of the information for the particular request for workers)
Other tables accessed include:
**tabMembershipList**
MemberID int
Name varchar
TownID int
**tabTown** (info on towns where our members live)
TownID int
Name varchar
Geo geography (coordinates)
The final product will get inserted into **tabCallList** with columns:
MemberID
CreateCallListID
StaffMemberID
The code for the stored procedure is below
CREATE PROC uspCreateCallList
--Declare variables
DECLARE @RadiusInMiles int;
DECLARE @StartTownID int;
DECLARE @StartTownGeo geography;
DECLARE @CreateCallListID int;
DECLARE @StaffMemberIDs table ([Value] varchar(max), [ordinal] bigint);
DECLARE @StaffMemberIDsCount int;
--Set variables
SET @CreateCallListID = (input from PowerApps);
SET @StartTownID = (input from PowerApps);
SET @RadiusInMiles = (input from PowerApps) * 1609.34;
-- I access my Person.Town table to get the coordinates that correspond to @TownID
SELECT @StartTownGeo = Geo
FROM tabTown
WHERE TownID = @TownID;
-- I create a table variable from the StaffMemberIDs column in tabCreateCallList
INSERT INTO @StaffMemberIDs([value], [ordinal])
SELECT [value], [ordinal] - 1
FROM tabCreateCallList
CROSS APPLY STRING_SPLIT(tabCreateCallList.StaffMemberIDs, ',', 1)
WHERE CreateCallListID = @CreateCallListID;
SELECT @StaffMemberIDsCount = COUNT(*) FROM @StaffMemberIDs ;
;WITH cte1(MemberID, CreateCallListID , StaffMemberPlaceholder)
AS(
select
MemberID,
@CreateCallListID CreateCallListID,
Rank() OVER (PARTITION BY p.TownID ORDER BY @StartTownGeo.STDistance(Geo)) % @StaffMemberIDsCount StaffMemberPlaceholder
from
tabMember m
JOIN tabTown t ON t.TownID = m.TownID
WHERE
@StartTownGeo.STDistance(Geo) <= @RadiusInMiles
),
cte2(MemberID, CreateCallListID, StaffMemberID)
AS(
SELECT
MemberID, CreateCallListID, [Value] StaffMemberID
FROM
cte1 a
JOIN @StaffMemberIDs b ON a.StaffMemberPlaceholder = b.Ordinal
)
INSERT INTO tabCallList ([MemberID], [CreateCallListID], [StaffMemberID])
SELECT
MemberID,
CreateCallListID,
StaffMemberID
FROM
cte2
It works. I just want to know: Is this a sane way to go about this problem? Am I missing something important.
Thanks so much for any guidance!!! 
