SP appears not to run

[macroCharlie]

I have a stored procedure that calls another stored procedure depending on certain variables and is supposed to run that called procedure, come back and run another SP. The first call seems to run fine, but the second doesn't seem to work accept in debugger. Here is the SP:

ALTER PROCEDURE dbo.usp_CreateRunSheet @MethodID int, @StudyTracker nvarchar(50), @MatrixID int
AS

DECLARE @OldID int
SELECT @OldID = IDENT_CURRENT('dbo.tblRunsheets')

DECLARE @strMsg varchar(100)
SET @strMsg = 'There are no samples that need RunSheets created for this Study and Method'

IF (SELECT COUNT(*)
FROM dbo.tblSampleMain
WHERE RunSheet_Created = 0
AND Method_ID = @MethodID
AND Study_Tracker = @StudyTracker
AND Matrix_ID = @MatrixID) < 1

BEGIN
RAISERROR 50001 @strMsg
SELECT @strMsg AS Message
RETURN 0
END
ELSE
BEGIN

TRUNCATE TABLE dbo.tblCreateRunSheet

IF @MethodID = 1
BEGIN
EXEC dbo.usp_CreateRunSheetOP @MethodID, @StudyTracker, @MatrixID
EXEC dbo.usp_CheckStatus @OldID
END
ELSE
IF @MethodID = 2
BEGIN
EXEC dbo.testcreaterunsheet @MethodID, @StudyTracker, @MatrixID
EXEC dbo.usp_CheckStatus @OldID
END
ELSE
BEGIN
EXEC dbo.usp_CreateElseRunsheet @MethodID, @StudyTracker, @MatrixID
EXEC dbo.usp_CheckStatus @OldID
END
END

The portion that doesn't seem to run is the second EXEC after each MethodID is checked. Should I move that procedure out of the BEGIN/END and move it to the last END?

 

[hmckillop]

Try

IF NOT EXISTS (SELECT 'X'
  FROM dbo.tblSampleMain
    WHERE RunSheet_Created = 0
    AND Method_ID = @MethodID
    AND Study_Tracker = @StudyTracker
        AND Matrix_ID = @MatrixID) 

    BEGIN
        RAISERROR 50001 @strMsg
        SELECT @strMsg AS Message
        RETURN 0
    END
ELSE
BEGIN

TRUNCATE TABLE dbo.tblCreateRunSheet

IF @MethodID = 1 
    BEGIN
        EXEC dbo.usp_CreateRunSheetOP @MethodID, @StudyTracker, @MatrixID
        EXEC dbo.usp_CheckStatus @OldID
    END
ELSE
    IF @MethodID = 2 
        BEGIN
            EXEC dbo.testcreaterunsheet @MethodID, @StudyTracker, @MatrixID
            EXEC dbo.usp_CheckStatus @OldID
        END
ELSE
    BEGIN
        EXEC dbo.usp_CreateElseRunsheet @MethodID, @StudyTracker, @MatrixID
        EXEC dbo.usp_CheckStatus @OldID
    END
END

But thats not the problem. You will only ever execute the SP.
What are you trying to do, which procs do you want to run.
If MethodID is 1 it will run
EXEC dbo.usp_CreateRunSheetOP @MethodID, @StudyTracker, @MatrixID
EXEC dbo.usp_CheckStatus @OldID

If its 2 it will run
EXEC dbo.testcreaterunsheet @MethodID, @StudyTracker, @MatrixID
EXEC dbo.usp_CheckStatus @OldID
And if its anything else it will run
EXEC dbo.usp_CreateElseRunsheet @MethodID, @StudyTracker, @MatrixID
EXEC dbo.usp_CheckStatus @OldID

The second proc in each condition will only be run if there isnt a serious error in the first proc.

"I'm living so far beyond my income that we may almost be said to be living apart