How to assign a variable to the execution of a stored procedure?

Question

I want to assign the value returned from the sp_formCreateEventID stored procedure into a new variable (@eventId). I initially thought this was the way to go. This solution is also in line with the EXEC command generated by SSMS.

However, for some reason the EXEC line returns an INT from the stored procedure as expected, but when it cant' assigned it's value to the @eventId variable.

DECLARE @eventId INT
EXEC @eventId = sp_formCreateEventID @patientId, @programId, @clinicianId, @formId, @phaseTypeId, @draft, @dataCollectionDate, NULL
SELECT @eventId

sp_formCreateEventID (don't hate me, I didn't write this...):

ALTER PROCEDURE  [dbo].[sp_formCreateEventID]
    @PatientID int,
    @ProgramID int,
    @ClinicianID int,
    @FormID int,
    @PhaseTypeID int,
    @Draft varchar(5),
    @CompletedDate varchar(40),
    @UserID int = null
AS
BEGIN


    IF @CompletedDate = ''
        SET @CompletedDate = NULL

    --for some forms such as Clinical Input - Initial, there should only have one form filled for a youth. If that is the case and the event has existed, just return that one.
    DECLARE @EID int
    SET @EID =  dbo.fn_GetExistingOnlyOneEventID(@PatientID, @ProgramID, @FormID)
    PRINT @eid
    IF @EID <> -99
    BEGIN
        SELECT 
        @EID AS 'EventID'
        RETURN
    END



    DECLARE @TxCycleID int
    DECLARE @TxCyclePhaseTypeID int
    DECLARE @TxCyclePhaseID int
    DECLARE @seqNum int
    DECLARE @NewEventID INT

    --if there is no cycle for this patient for this program, then create one.
    IF NOT EXISTS (SELECT * FROM TxCycle WHERE PatientID = @PatientID AND ProgID = @ProgramID)
    BEGIN
        INSERT INTO TxCycle
        (OpenDate, PatientID, ProgID)
        VALUES
        (GETDate(), @PatientID, @ProgramID)
    END

    SELECT 
        @TxCycleID = Max(TxCycleID)
    FROM TxCycle
    WHERE
        PatientID = @PatientID AND 
        ProgID = @ProgramID

    --In this cycle, for the current phase type, get the max seq number
    IF EXISTS (SELECT * FROM TxCyclePhase WHERE TxCycle = @TxCycleID)
    BEGIN
        SELECT 
            @seqNum = MAX(SeqNum)
        FROM
            TxCyclePhase
        WHERE
            TxCycle = @TxCycleID

        SET @seqNum = @seqNum + 1
    END
    ELSE
    BEGIN
        SET @seqNum = 1
    END

    PRINT 'New Seq Num: ' + CONVERT(Varchar(5),@seqNum)

    --greate a new seq number under the same phase
    INSERT INTO TxCyclePhase
    (Type, seqNum, TxCycle)
    VALUES
    (@PhaseTypeID, @seqNum, @TxCycleID)

    --get the new ID, this will be used for the Event
    SELECT
        @TxCyclePhaseID =  Max(TxCyclePhaseID)
    FROM
        TxCyclePhase

    DECLARE @isFinal int
    IF @Draft = 'Yes'
    BEGIN
        SET @isFinal = 0
    END
    ELSE
    BEGIN
        SET @isFinal = 1
    END

    IF EXISTS(SELECT * FROM LoginPassword WHERE ClinID = @ClinicianID AND AccessID IN (1,3))
    BEGIN
        IF NOT EXISTS (SELECT * FROM ClinPat WHERE ClinID = @ClinicianID AND PatientID = @PatientID)
        BEGIN
            INSERT INTO 
                ClinPat
            (ClinID, PatientID)
            VALUES
            (@ClinicianID, @PatientID)
        END
    END

    INSERT INTO FormPat
    (PatientID, ClinID, FormID, TxCyclePhase, Date, Final, DataCollectionDate)
    VALUES
    (@PatientID, @ClinicianID, @FormID, @TxCyclePhaseID, GETDATE(), @isFinal, @CompletedDate)



    SELECT @NewEventID = Scope_Identity()
    SELECT @NewEventID AS 'EventID'

What am I doing wrong?

Answer 1

You need a RETURN at the bottom of your procedure.

RETURN @NewEventID

Here is a complete but simple example:

CREATE PROCEDURE [dbo].[uspExampleOne] 
@Parameter1 INT
AS

BEGIN

SET NOCOUNT ON

RETURN 333

SET NOCOUNT OFF

END
GO

and

Declare @MyValue INT
EXEC @MyValue = [dbo].[uspExampleOne]  111
SELECT '@MyValueHere' = @MyValue 

Result:

@MyValueHere
333

But a better design IMHO is to use an OUTPUT variable:

Why?

What happens when you need a second OUTPUT? What happens when the needed value is not an INT?

ALTER PROCEDURE [dbo].[uspExampleOne] 
@Parameter1 INT ,
@OutParameter2 INT OUT
AS

BEGIN

SET NOCOUNT ON

Select @OutParameter2 = 444

RETURN 333

SET NOCOUNT OFF

END
GO

and

Declare @MyValue INT
Declare @OutParameterTwo INT

EXEC @MyValue = [dbo].[uspExampleOne]  111 , @OutParameterTwo OUT
SELECT '@MyValueHere' = @MyValue 

Select '@OutParameterTwoHere' = @OutParameterTwo

Output

@MyValueHere
333
@OutParameterTwoHere
444

Below shows what I mean about "future proofing" with OUTPUT parameters

ALTER PROCEDURE [dbo].[uspExampleOne] 
@Parameter1 INT ,
@OutParameter2 INT OUT,
@OutParameter3 VARCHAR(128) OUT
AS

BEGIN

SET NOCOUNT ON

Select @OutParameter2 = 444
Select @OutParameter3 = 'Better Design With Output Parameters.  Not stuck with 1 return-value or data-type'

RETURN 0 /* everything went ok */

SET NOCOUNT OFF

END
GO

and the call to it

Declare @MyValue INT
Declare @OutParameterTwo INT
Declare @OutParameterThree VARCHAR(128)

EXEC @MyValue = [dbo].[uspExampleOne]  111 , @OutParameterTwo OUT , @OutParameterThree OUT
SELECT '@MyValueHere' = @MyValue 

Select '@OutParameterTwoHere' = @OutParameterTwo , '@OutParameterThreeHere' = @OutParameterThree

and output

@OutParameterTwoHere    @OutParameterThreeHere
444                     Better Design With Output Parameters.  Not stuck with 1 return-value or data-type