SQL Service Reference generates execute method instead of List

  • 6 March 2017
  • 0 replies
  • 51 views

Badge +7

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.

 

 


0 replies

Be the first to reply!

Reply