Symptoms

We have a procedure that return a list but when we add a block containing IF ... ELSE IF ... this procedure is then not detected as "List" but as "Execute". ALTER PROCEDURE dbo]..TheProcedureName] @P_FDWorkflowPK bigint = 0, @P_StepName nvarchar(MAX) = '' AS BEGIN SET NOCOUNT ON declare @CountToReview decimal = 0 declare @CountApproved1st decimal = 0 declare @CountApproved2nd decimal = 0 declare @CountSignatureCheck decimal = 0 declare @ProgressionPourcentage decimal = 0 declare @ResultHtml nvarchar(MAX)='' --.... -- START OR REGION that generate the issue of procedure detected as "Execute" instead of "List" -- IF (@CountToReview > 0 AND @P_StepName LIKE 'FIRST') -- set @ProgressionPourcentage = ((@CountApproved1st/@CountToReview)*100) --ELSE IF (@CountToReview > 0 AND @P_StepName LIKE 'SECOND') -- set @ProgressionPourcentage = ((@CountApproved2nd/@CountToReview)*100) --ELSE IF (@CountToReview > 0 AND @P_StepName LIKE 'CHECKING_SIGNATURE') -- set @ProgressionPourcentage = ((@CountSignatureCheck/@CountToReview)*100) --ELSE set @ProgressionPourcentage = -30 -- END REGION --.... SELECT @CountToReview as CountToReview, @CountApproved1st as CountApproved1st, @CountApproved2nd as CountApproved2nd, @legend as Legend @ResultHtml as ResultHtml END
 

Diagnoses

The service instance of SQL server discover the procedure using FMTONLY
 

Resolution

The following was added to the stored procedure to work around the FMTONLY option
CREATE PROCEDURE.... AS

DECLARE @fmtonlyON BIT
Set @fmtonlyON = 0
IF (1=0) SET @fmtonlyON = 1
SET FMTONLY OFF

..... (body of stored proc)...

IF @fmtonlyOn = 1

SET FMTONLY ON

END

This causes execution when registering the service instance to follow this path:

SET FMTONLY ON

exec StoredProcedure()

(inside stored proc)
SET FMTONLY OFF
(body of stored proc)
SET FMTONLY ON

(stored proc completes)
SET FMTONLY OFF

Further details on this behavior can be found in http://help.k2.com/kb001451. This KB article does not describe this particular workaround, but rather offers a few other options on resolving this issue.