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: 268

Filed under:
|

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:

my database

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about tsql