Return Value Of Stored Procedure

[mcfly1204]

I am executing a stored procedure that will always return a value, specifically an integer. The error handling within the stored procedure will return 0 if succesful, 1 on an error, and 2 for a duplicate. How to I capture the returned integer?

int ReturnValue = cmd.ExecuteNonQ uery(); will return the number of rows effected, but I just want the simple return value. I did not think I would need an output parameter to perform this, what am I missing here?

[Plater]

You are right. You do not need a parameter of type output. You need one of type Return_Value

[mcfly1204]

I am not sure what I am missing, but I cannot return anything but -1.

                            cmd = new SqlCommand("spCreateOpportunity", slxConn2);
                            cmd.CommandType = CommandType.StoredProcedure;
                            SqlParameter returnParm = cmd.Parameters.Add("@retvalue", SqlDbType.Int);
                            returnParm.Direction = ParameterDirection.ReturnValue;
                            cmd.Parameters.AddWithValue("@opportunityid", OpportunityID);
                            cmd.Parameters.AddWithValue("@userid", "Admin");
                            
                            slxConn2.Open();

                            int returnValue = cmd.ExecuteNonQuery();

I suppose this could be an issue with the stored procedure and not how I am calling it.

[Plater]

What does your stored procedure look like?
After you call execute, cmd.Parameters( "@retvalue" ) should have your return value.

[mcfly1204]

CREATE PROCEDURE spCreateOpportunity
(
@opportunityid	char(12),
@userid		char(12)
)

AS

------------------------------------------------------------------------------------------------------------------------------------------------------------------  
--Name:  spCreateOpportunity  
--Description:  Takes values from tblCreateOppWrk and creates the opportunity as well   
--Author:  AM  
--Modification Log: Change  
  
--Description                  			Date         	Changed By  
--Created procedure     			2/13/2009   	AM
--Added @userid input parameter so that the 	2/17/2009	AM
--stored procedure can be more flexible for 
--other applications requiring the creation
--of an opportunity.   
-----------------------------------------------------------------------------------------------------------------------------------------------------------------  
BEGIN

DECLARE

@description	varchar(64),
@soshipdate	datetime,
@sodate 	datetime,
@accountmanagerid	char(12),
@assignedaccountid	char(12),
@notes		varchar(600),
@shipvia	varchar(32),
@shipaddress1	varchar(50),
@shipaddress2 	varchar(40),
@shipaddress3	varchar(40),
@shipaddress4 	varchar(50),
@shipcity	varchar(50),
@shipstate	varchar(32),
@shipzip	varchar(32),
@shipcountry	varchar(50),
@ship_name	varchar(50),
@inhandsdate	datetime,
@accountid	char(12),
@account	char(128),
@oppcontactid	char(12),
@contactid	char(12),
@firstname	char(32),
@lastname	char(32),
@attachid	char(12),
@filesize 	int,
@filename	varchar(255),
@err		int,
@returnvalue	int

--select values from work table and assign variable values
SELECT @description=DESCRIPTION,@soshipdate=SOSHIPDATE,@accountmanagerid=ACCOUNTMANAGERID,@notes=NOTES, 
	@shipvia=SHIPVIA,@shipaddress1=SHIPADDRESS1,@shipaddress2=SHIPADDRESS2,@shipaddress3=SHIPADDRESS3,@shipaddress4=SHIPADDRESS4,@shipcity=SHIPCITY,
	@shipstate=SHIPSTATE,@shipzip=SHIPZIP,@shipcountry=SHIPCOUNTRY,@ship_name=SHIP_NAME,@inhandsdate=INHANDSDATE,@account=ACCOUNT,
	@oppcontactid=OPPCONTACTID,@firstname=FIRSTNAME,@lastname=LASTNAME,@attachid=ATTACHID,@filesize=FILESIZE,@filename=FILENAME
FROM tblCreateOppWrk
WHERE OPPORTUNITYID = @opportunityid


--select accountid from the account table
SELECT @accountid = ACCOUNTID
FROM SYSDBA.ACCOUNT
WHERE ACCOUNT = @account


--check for duplicate purchase orders
IF EXISTS (SELECT OPPORTUNITYID FROM sysdba.OPPORTUNITY WHERE DESCRIPTION=@description and ACCOUNTID=@accountid)
  BEGIN
  SET @returnvalue=2
  RETURN @returnvalue
  END
ELSE
  --begin opportunity inserts


--insert values into the opportunity table
BEGIN TRANSACTION
INSERT INTO [sysdba].[OPPORTUNITY] 
(OPPORTUNITYID, ACCOUNTID, DESCRIPTION, CLOSED, ACCOUNTMANAGERID, STATUS, SECCODEID, CREATEUSER, CREATEDATE,MODIFYUSER, MODIFYDATE) 
VALUES (@opportunityid, @accountid, @description, 'F', @accountmanagerid, 'PENDING	', 'SYST00000001', @userid, GetDate(), @userid, GetDate())

SELECT @err = @@error
IF @err = 0 
  BEGIN
  IF @@trancount>0 COMMIT TRANSACTION
  END
ELSE
  BEGIN
  IF @@trancount>0 ROLLBACK TRANSACTION
  SET @returnvalue=1
  RETURN @returnvalue
  END


--insert values into the qky_opportunity_ext table
INSERT INTO sysdba.QKY_OPPORTUNITY_EXT (OPPORTUNITYID,CREATEUSER,CREATEDATE,MODIFYUSER,MODIFYDATE,SODATE,ASSIGNEDACCOUNTID,NOTES,SHIPADDRESS1,SHIPADDRESS2,SHIPADDRESS3,SHIPCITY,SHIPSTATE,SHIPZIP,SHIPADDRESS4,SHIPCOUNTRY,SHIP_NAME,SOSHIPDATE,OPPORTUNITY_ROUTE) 
VALUES (@opportunityid,@userid,GetDate(),@userid,GetDate(),@sodate,@assignedaccountid,@notes,@shipaddress1,@shipaddress2,@shipaddress3,@shipcity,@shipstate,@shipzip,@shipaddress4,@shipcountry,@ship_name,@soshipdate,'Mac File')

SELECT @err = @@error
IF @err = 0 
  BEGIN
  IF @@trancount>0 COMMIT TRANSACTION
  END
ELSE
  BEGIN
  IF @@trancount>0 ROLLBACK TRANSACTION
  SET @returnvalue=1
  RETURN @returnvalue
  END


--select the contact id
SELECT @contactid = CONTACTID 
FROM sysdba.CONTACT 
WHERE FIRSTNAME=@firstname AND LASTNAME=@lastname AND ACCOUNTID=@accountid

--insert contact into opportunity_contact table if available
IF (@contactid IS NOT NULL)
INSERT INTO sysdba.OPPORTUNITY_CONTACT (OPPCONTACTID,CONTACTID,OPPORTUNITYID,CREATEUSER,CREATEDATE,MODIFYUSER,MODIFYDATE) 
VALUES (@oppcontactid,@contactid,@opportunityid,@userid,GetDate(),@userid,GetDate())

SELECT @err = @@error
IF @err = 0 
  BEGIN
  IF @@trancount>0 COMMIT TRANSACTION
  END
ELSE
  BEGIN
  IF @@trancount>0 ROLLBACK TRANSACTION
  SET @returnvalue=1
  RETURN @returnvalue
  END



--insert values into the attachment table
INSERT INTO sysdba.ATTACHMENT (ATTACHID, ATTACHDATE, ACCOUNTID, OPPORTUNITYID, DESCRIPTION, DATATYPE, FILESIZE, FILENAME, USERID)
VALUES (@attachid,GetDate(),@accountid,@opportunityid,@description,'R',@filesize,@filename,@userid)

SELECT @err = @@error
IF @err = 0 
  BEGIN
  IF @@trancount>0 COMMIT TRANSACTION
  END
ELSE
  BEGIN
  IF @@trancount>0 ROLLBACK TRANSACTION
  SET @returnvalue=1
  RETURN @returnvalue
  END
SET @returnvalue=0
RETURN @returnvalue
END


GO

[Plater]

Well there appear to be a number of places where your returnvalue would never be set, but returned anyway.

[code=SQL]
IF @err = 0
BEGIN
IF @@trancount>0 COMMIT TRANSACTION
END
ELSE
BEGIN
IF @@trancount>0 ROLLBACK TRANSACTION
SET @returnvalue=1
RETURN @returnvalue
END
[/code]
if @err != 0 and @@trancount is not greater then zero, returnvalue is never set

[mcfly1204]

I thought that adding a second ELSE clause would catch those value, but the procedure continues to return -1.

SELECT @err = @@ERROR
IF @err = 0  
   BEGIN 
   IF @@trancount>0 COMMIT TRANSACTION 
   END 
ELSE 
   IF @@trancount>0 --ROLLBACK TRANSACTION
     BEGIN
     SET @returnvalue=1 
     RETURN @returnvalue
     ROLLBACK TRANSACTION
     END
   ELSE
     BEGIN
     SET @returnvalue=1 
     RETURN @returnvalue
     END

On a sidenote, if I stripped everything out of the stored procedure so that it only contained a return statement, shouldn't it return that value?

CREATE PROCEDURE spCreateOpportunity
(
@opportunityid	char(12),
@userid		char(12)
)
AS
BEGIN
RETURN 5
END
GO

[Plater]

Yes, your last example should return a 5

[mcfly1204]

Yes, your last example should return a 5

The second line was needed, the return value was never being assigned to int returnValue. I appreciate your help with this Plater.

cmd.ExecuteNonQuery();
int returnValue = (int)returnParm.Value;
Console.WriteLine("Return value: {0}", returnValue);