CODEWITHSUNDEEP
stored-procedurescoldfusion
Cmaso
Cmaso

Reputation: 1113

Can you access the return of a stored proc using cfstoredproc, even if return is not defined as an out parameter?

I have a stored procedure whose return I want to access, using the Coldfusion cfstoredproc tag. However, the return variable is not listed as an out param, along with the 6 "in" params. See procedure's code below:

CREATE PROCEDURE [dbo].[sp_Hire_AddEVerifyEmpCloseCase]
(   
           @e_verify_id bigint
           ,@ClientSftwrVer varchar(30)=null
           ,@CaseNbr char(15)
           ,@CloseStatus varchar(50)
           ,@CurrentlyEmployed varchar(1)
           ,@submit_user_id int
           //THIS LINE IS MISSING: @EmpCloseCase_Id int OUTPUT
)
AS
BEGIN

    DECLARE @EmpCloseCase_id int
    SET @EmpCloseCase_id=0
    
    SELECT @EmpCloseCase_id = EmpCloseCase_id FROM Appl_Hired_EVerify_EmpCloseCase WITH(NOLOCK) WHERE e_verify_id = @e_verify_id

    BEGIN TRANSACTION EmpCloseCase
    BEGIN TRY
        IF(@EmpCloseCase_id = 0) BEGIN
            INSERT INTO Appl_Hired_EVerify_EmpCloseCase(e_verify_id,ClientSftwrVer,CaseNbr,CloseStatus,CurrentlyEmployed, systemdate,submit_user_id)
            VALUES (@e_verify_id,@ClientSftwrVer,@CaseNbr,@CloseStatus,@CurrentlyEmployed,GETDATE(),@submit_user_id)
            
            SET @EmpCloseCase_id=ISNULL(SCOPE_IDENTITY(),0)
        END ELSE BEGIN
            UPDATE Appl_Hired_EVerify_EmpCloseCase
            SET ClientSftwrVer = @ClientSftwrVer,
                CaseNbr = @CaseNbr,
                CloseStatus = @CloseStatus,
                CurrentlyEmployed = @CurrentlyEmployed, 
                systemdate = GETDATE(),
                submit_user_id = @submit_user_id
            WHERE EmpCloseCase_id = @EmpCloseCase_id
        END

        COMMIT TRANSACTION EmpCloseCase
    END TRY
    BEGIN CATCH
           SET @EmpCloseCase_id=0
           ROLLBACK TRANSACTION  EmpCloseCase
    END CATCH

   RETURN @EmpCloseCase_id
END

Because that "OUTPUT" line is missing, it throws an error if I try to include <cfprocparam type="out" variable="empCloseCaseId"> in my cfstoredproc. Is there any way to access/store the value of this return variable @EmpCloseCase_id, using cfstoredproc, without having to add in that missing "OUTPUT" line or otherwise change the proc's code?

Upvotes: 2

Views: 496

Answers (1)

Adrian J. Moreno
Adrian J. Moreno

Reputation: 14859

Change

RETURN @EmpCloseCase_id

to

SELECT @EmpCloseCase_id as emp_close_case_id

and in your cfstoredproc call, add

<cfprocresult name="foo">

This defines the variable foo as a query with a single row and a column emp_close_case_id.

<cfoutput>
    #foo.emp_close_case_id#
</cfoutput>

EDIT: No way to access that data without properly declaring the output variable or returning a data set with a select statement. SQL Server docs: Return Data from a Stored Procedure

Upvotes: 3

Related Questions