Help Troubleshooting this T-SQL


I need to get some help troubleshooting this T-SQL. I have a stored
procedure that gets a list of all open tickets in the system. I load them
into a temp table and go through each row in the temp table to create a
messagebody and message subject for email. Next I call a procedure to send
the email to the group.

Here is the procedure that calls the open tickets:

BEGIN
DECLARE @EPNNoEmail int
DECLARE @Msg varchar(1000)
DECLARE @Subject varchar(200)
DECLARE @DeptID int
DECLARE @i int
DECLARE @Max int
SET @i = 1
DECLARE @EPNID int
DECLARE @EPNMsg table (EPNMsgID int identity, EPN int, Condition
varchar(2000),Dept varchar(20), Cell varchar(20))
SET @EPNNoEMail = (SELECT Count(EPNID) FROM EPN WHERE NotificationSent = 0)
IF @EPNNoEmail > 0
BEGIN
--Process
INSERT INTO @EPNMsg (EPN, Condition, Dept, Cell)
SELECT
EPN.EPNID,
EPN.Condition,
Dept.Department,
Cell.Cell
FROM
EPN INNER JOIN
Dept ON EPN.DeptID = Dept.DeptID INNER JOIN
Cell ON EPN.CellID = Cell.CellID
WHERE
NotificationSent = 0
SET @Max = (SELECT MAX(EPNMsgID) FROM @EPNMsg)
WHILE @i <= @Max
BEGIN
SET @Msg = (SELECT 'Observed Condition: ' + Condition FROM @EPNMsg
WHERE EPNMsgID = @i)
SET @Subject = (SELECT 'EPN Created in dept: ' + dept + ' at cell ' +
cell FROM @EPNMsg WHERE EPNMsgID = @i)
SET @EPNID = (SELECT EPN FROM @EPNMsg WHERE EPNMsgID = @i)
--Notify Engineering
EXEC SendEPNNotification @EPNID,@Msg,@Subject,3
--Notify Supervisor
EXEC SendEPNNotification @EPNID,@Msg,@Subject,8
--Nofity Manager
EXEC SendEPNNotification @EPNID,@Msg,@Subject,7
--Update the EPN as contacted
UPDATE
EPN
SET
NotificationSent = 1
WHERE
EPNID = @EPNID

SET @i = @i + 1
END
END
END

When I step through this procedure, the Msg and Subject do not change, the
EPNID does.

Next I call this stored proc to email the recipients:

ALTER PROCEDURE [dbo].[SendEPNNotification]
@EPNID int,
@Msg varchar(2000),
@mySubject varchar(200),
@ContactID int

AS
BEGIN
--Check the contact ID. If it is 99 then set
DECLARE @Email varchar(100)
DECLARE @Name varchar(50)
DECLARE @txtMail varchar(100)


--Contact ID 13 is wzstaff, if this is the case, send the email regardless
of the department
IF @ContactID <> 13
BEGIN
DECLARE PersonCursor CURSOR FAST_FORWARD FOR
SELECT
DeptContact.ContactName,
DeptContact.ContactEmail,
DeptContact.textEmail
FROM
DeptContact
INNER JOIN Dept ON DeptContact.DeptID = Dept.DeptID
INNER JOIN EPN ON Dept.DeptID = EPN.DeptID
WHERE
EPN.EPNID =@EPNID
and
DeptContact.ContactTypeID= @ContactID

OPEN PersonCursor
FETCH NEXT FROM PersonCursor
INTO @Name, @Email,@txtMail

While @@Fetch_Status = 0
BEGIN
EXEC msdb.dbo.sp_Send_dbmail
@profile_name='WZSQLAdmin',
@Recipients = @email,
@Subject = @mySubject,
@body = @Msg,
@body_format='HTML'
--Check to see if there is a text email
IF @txtMail is not null
BEGIN
EXEC msdb.dbo.sp_Send_dbmail
@profile_name='WZSQLAdmin',
@Recipients = @txtMail,
@Subject = @mySubject,
@body = @Msg,
@body_format='HTML'
END
FETCH NEXT FROM PersonCursor
INTO @Name, @Email
END
END
ELSE IF @ContactID = 13
BEGIN
DECLARE PersonCursor CURSOR FAST_FORWARD FOR
SELECT
DeptContact.ContactName,
DeptContact.ContactEmail,
DeptContact.textEmail
FROM
DeptContact
WHERE
DeptContact.DeptID= 99

OPEN PersonCursor
FETCH NEXT FROM PersonCursor
INTO @Name, @Email,@txtMail

While @@Fetch_Status = 0
BEGIN
EXEC msdb.dbo.sp_Send_dbmail
@profile_name='[Profile Name]',
@Recipients = @email,
@Subject = @mySubject,
@body = @Msg,
@body_format='HTML'
--Check to see if there is a text email
IF @txtMail is not null
BEGIN
EXEC msdb.dbo.sp_Send_dbmail
@profile_name='[profilename]',
@Recipients = @txtMail,
@Subject = @mySubject,
@body = @Msg,
@body_format='HTML'
END
FETCH NEXT FROM PersonCursor
INTO @Name, @Email
END
END
CLOSE PersonCursor
DEALLOCATE PersonCursor


END

This procedure will only send to the first person in the list (in the
PresonCursor). It will not fetch the next record. Can anyone see why?

Thanks.

John
.

Relevant Pages
Loading