Stored Procedure Does Not Fire Last Command
Posted
by
jp2code
on Stack Overflow
See other posts from Stack Overflow
or by jp2code
Published on 2012-06-21T21:34:12Z
Indexed on
2012/06/22
21:16 UTC
Read the original article
Hit count: 265
sql-server
|tsql
On our SQL Server (Version 10.0.1600), I have a stored procedure that I wrote.
It is not throwing any errors, and it is returning the correct values after making the insert in the database.
However, the last command spSendEventNotificationEmail
(which sends out email notifications) is not being run.
I can run the spSendEventNotificationEmail
script manually using the same data, and the notifications show up, so I know it works.
Is there something wrong with how I call it in my stored procedure?
[dbo].[spUpdateRequest](@packetID int, @statusID int output, @empID int, @mtf nVarChar(50)) AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @id int
SET @id=-1
-- Insert statements for procedure here
SELECT A.ID, PacketID, StatusID
INTO #act FROM Action A JOIN Request R ON (R.ID=A.RequestID)
WHERE (PacketID=@packetID) AND (StatusID=@statusID)
IF ((SELECT COUNT(ID) FROM #act)=0) BEGIN -- this statusID has not been entered. Continue
SELECT ID, MTF
INTO #req FROM Request
WHERE PacketID=@packetID
WHILE (0 < (SELECT COUNT(ID) FROM #req)) BEGIN
SELECT TOP 1 @id=ID FROM #req
INSERT INTO Action (RequestID, StatusID, EmpID, DateStamp)
VALUES (@id, @statusID, @empID, GETDATE())
IF ((@mtf IS NOT NULL) AND (0 < LEN(RTRIM(@mtf)))) BEGIN
UPDATE Request SET MTF=@mtf WHERE ID=@id
END
DELETE #req WHERE ID=@id
END
DROP TABLE #req
SELECT @id=@@IDENTITY, @statusID=StatusID FROM Action
SELECT TOP 1 @statusID=ID FROM Status
WHERE (@statusID<ID) AND (-1 < Sequence)
EXEC spSendEventNotificationEmail @packetID, @statusID, 'http:\\cpweb:8100\NextStep.aspx'
END ELSE BEGIN
SET @statusID = -1
END
DROP TABLE #act
END
Idea of how the data tables are connected:
© Stack Overflow or respective owner