There are flaws in your approach, Instead of iterating over the HR Admin users you should have iterated over Documents. Let's do a step by step optimization iteration over your code
Correction and first Optimization Iteration
DECLARE @TBL TABLE
(
TRID INT,
DOCUMENT_NAME VARCHAR(100),
EMP_NAME VARCHAR(100),
TO_NAME VARCHAR(100),
MESSAGE_BODY VARCHAR(1024)
)
INSERT INTO @TBL(TRID, DOCUMENT_NAME, EMP_NAME, TO_NAME, MESSAGE_BODY)
Select TDR.TrID, TDR.DocumentCode, EM.NAME, '', '<html><body><p>Hi ' + EM.NAME + ',</p><p>Document Delivery Alert - ' + TDR.DocumentCode + '</p><p>Please Deliver the Appropriate Document To ' + @toname + '</p></body></html>'
From (
SELECT DISTINCT TRID
FROM TrDocumentRequest
Where EmailStatus IS NULL
) INNER_TMP_TABLE INNER JOIN TrDocumentRequest TDR ON INNER_TMP_TABLE.TRID = TDR.TRID
INNER JOIN EmployeeMaster EM ON TDR.RequestBy = EM.EmpID
DECLARE ADMX CURSOR FOR SELECT Name, EmailID FROM EmployeeMaster WHERE Active = 'Y' AND Department='HR'
OPEN ADMX
FETCH ADMX INTO @AdminName, @AdminEmail
WHILE @@fetch_status = 0
BEGIN
INSERT INTO [EmailSend]
([EmailDate],[EmailFromName],[EmailFromID],[EmpID],[EmailToName],[EmailToID],[Subject],
[Message],[EmailType],[ReadIn],[ReadOut],[Deleted],[Sent],[RefTrID],[Retry])
Select GETDATE(),'Document Delivery Request','aa@aaa.com',@RequestBy,@AdminName,@AdminEmail,
'Document Delivery Request',MESAGE_BODY,'DOC','N','N','N','N',TRID,0
FROM @TBL
FETCH ADMX INTO @AdminName, @AdminEmail
END
CLOSE ADMX
DEALLOCATE ADMX
In production you will always have more Document Requests which have not been processed by your email processing service. Since this is independent of the context (means not depending over users) a better approach would be to hold all candidate document requests in a temp location
and generating message body as well in the same time and do a bulk insert process like shown above
Second Optimization Iteration
If you closely look at the code, you can acknowledge that the whole loop can be eliminated. The loop is simply fetching hr admin users and tagging it to document requests in message queue.
DECLARE @TBL TABLE
(
TRID INT,
DOCUMENT_NAME VARCHAR(100),
EMP_NAME VARCHAR(100),
TO_NAME VARCHAR(100),
MESSAGE_BODY VARCHAR(1024)
)
INSERT INTO @TBL(TRID, DOCUMENT_NAME, EMP_NAME, TO_NAME, MESSAGE_BODY)
Select TDR.TrID, TDR.DocumentCode, EM.NAME, '', '<html><body><p>Hi ' + EM.NAME + ',</p><p>Document Delivery Alert - ' + TDR.DocumentCode + '</p><p>Please Deliver the Appropriate Document To ' + @toname + '</p></body></html>'
From (
SELECT DISTINCT TRID
FROM TrDocumentRequest
Where EmailStatus IS NULL
) INNER_TMP_TABLE INNER JOIN TrDocumentRequest TDR ON INNER_TMP_TABLE.TRID = TDR.TRID
INNER JOIN EmployeeMaster EM ON TDR.RequestBy = EM.EmpID
INSERT INTO [EmailSend]
([EmailDate],[EmailFromName],[EmailFromID],[EmpID],[EmailToName],[EmailToID],[Subject],
[Message],[EmailType],[ReadIn],[ReadOut],[Deleted],[Sent],[RefTrID],[Retry])
Select GETDATE(),'Document Delivery Request','aa@aaa.com',@RequestBy,Name,EmailID,
'Document Delivery Request',MESSAGE_BODY,'DOC','N','N','N','N',TRID,0
FROM @TBL T CROSS JOIN
(SELECT Name, EmailID FROM EmployeeMaster WHERE Active = 'Y' AND Department='HR') A
Update TrDocumentRequest Set EmailStatus='Y' Where EmailStatus IS NULL
I have also noticed that some variables are not initialized appropriately like @RequestBy