By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,649 Members | 696 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,649 IT Pros & Developers. It's quick & easy.

Return Value Of Stored Procedure

100+
P: 233
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.ExecuteNonQuery(); 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?
Feb 26 '09 #1
Share this Question
Share on Google+
8 Replies


Plater
Expert 5K+
P: 7,872
You are right. You do not need a parameter of type output. You need one of type Return_Value
Feb 26 '09 #2

100+
P: 233
I am not sure what I am missing, but I cannot return anything but -1.

Expand|Select|Wrap|Line Numbers
  1.                             cmd = new SqlCommand("spCreateOpportunity", slxConn2);
  2.                             cmd.CommandType = CommandType.StoredProcedure;
  3.                             SqlParameter returnParm = cmd.Parameters.Add("@retvalue", SqlDbType.Int);
  4.                             returnParm.Direction = ParameterDirection.ReturnValue;
  5.                             cmd.Parameters.AddWithValue("@opportunityid", OpportunityID);
  6.                             cmd.Parameters.AddWithValue("@userid", "Admin");
  7.  
  8.                             slxConn2.Open();
  9.  
  10.                             int returnValue = cmd.ExecuteNonQuery();
I suppose this could be an issue with the stored procedure and not how I am calling it.
Feb 26 '09 #3

Plater
Expert 5K+
P: 7,872
What does your stored procedure look like?
After you call execute, cmd.Parameters("@retvalue") should have your return value.
Feb 26 '09 #4

100+
P: 233
Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE spCreateOpportunity
  2. (
  3. @opportunityid    char(12),
  4. @userid        char(12)
  5. )
  6.  
  7. AS
  8.  
  9. ------------------------------------------------------------------------------------------------------------------------------------------------------------------  
  10. --Name:  spCreateOpportunity  
  11. --Description:  Takes values from tblCreateOppWrk and creates the opportunity as well   
  12. --Author:  AM  
  13. --Modification Log: Change  
  14.  
  15. --Description                              Date             Changed By  
  16. --Created procedure                 2/13/2009       AM
  17. --Added @userid input parameter so that the     2/17/2009    AM
  18. --stored procedure can be more flexible for 
  19. --other applications requiring the creation
  20. --of an opportunity.   
  21. -----------------------------------------------------------------------------------------------------------------------------------------------------------------  
  22. BEGIN
  23.  
  24. DECLARE
  25.  
  26. @description    varchar(64),
  27. @soshipdate    datetime,
  28. @sodate     datetime,
  29. @accountmanagerid    char(12),
  30. @assignedaccountid    char(12),
  31. @notes        varchar(600),
  32. @shipvia    varchar(32),
  33. @shipaddress1    varchar(50),
  34. @shipaddress2     varchar(40),
  35. @shipaddress3    varchar(40),
  36. @shipaddress4     varchar(50),
  37. @shipcity    varchar(50),
  38. @shipstate    varchar(32),
  39. @shipzip    varchar(32),
  40. @shipcountry    varchar(50),
  41. @ship_name    varchar(50),
  42. @inhandsdate    datetime,
  43. @accountid    char(12),
  44. @account    char(128),
  45. @oppcontactid    char(12),
  46. @contactid    char(12),
  47. @firstname    char(32),
  48. @lastname    char(32),
  49. @attachid    char(12),
  50. @filesize     int,
  51. @filename    varchar(255),
  52. @err        int,
  53. @returnvalue    int
  54.  
  55. --select values from work table and assign variable values
  56. SELECT @description=DESCRIPTION,@soshipdate=SOSHIPDATE,@accountmanagerid=ACCOUNTMANAGERID,@notes=NOTES, 
  57.     @shipvia=SHIPVIA,@shipaddress1=SHIPADDRESS1,@shipaddress2=SHIPADDRESS2,@shipaddress3=SHIPADDRESS3,@shipaddress4=SHIPADDRESS4,@shipcity=SHIPCITY,
  58.     @shipstate=SHIPSTATE,@shipzip=SHIPZIP,@shipcountry=SHIPCOUNTRY,@ship_name=SHIP_NAME,@inhandsdate=INHANDSDATE,@account=ACCOUNT,
  59.     @oppcontactid=OPPCONTACTID,@firstname=FIRSTNAME,@lastname=LASTNAME,@attachid=ATTACHID,@filesize=FILESIZE,@filename=FILENAME
  60. FROM tblCreateOppWrk
  61. WHERE OPPORTUNITYID = @opportunityid
  62.  
  63.  
  64. --select accountid from the account table
  65. SELECT @accountid = ACCOUNTID
  66. FROM SYSDBA.ACCOUNT
  67. WHERE ACCOUNT = @account
  68.  
  69.  
  70. --check for duplicate purchase orders
  71. IF EXISTS (SELECT OPPORTUNITYID FROM sysdba.OPPORTUNITY WHERE DESCRIPTION=@description and ACCOUNTID=@accountid)
  72.   BEGIN
  73.   SET @returnvalue=2
  74.   RETURN @returnvalue
  75.   END
  76. ELSE
  77.   --begin opportunity inserts
  78.  
  79.  
  80. --insert values into the opportunity table
  81. BEGIN TRANSACTION
  82. INSERT INTO [sysdba].[OPPORTUNITY] 
  83. (OPPORTUNITYID, ACCOUNTID, DESCRIPTION, CLOSED, ACCOUNTMANAGERID, STATUS, SECCODEID, CREATEUSER, CREATEDATE,MODIFYUSER, MODIFYDATE) 
  84. VALUES (@opportunityid, @accountid, @description, 'F', @accountmanagerid, 'PENDING    ', 'SYST00000001', @userid, GetDate(), @userid, GetDate())
  85.  
  86. SELECT @err = @@error
  87. IF @err = 0 
  88.   BEGIN
  89.   IF @@trancount>0 COMMIT TRANSACTION
  90.   END
  91. ELSE
  92.   BEGIN
  93.   IF @@trancount>0 ROLLBACK TRANSACTION
  94.   SET @returnvalue=1
  95.   RETURN @returnvalue
  96.   END
  97.  
  98.  
  99. --insert values into the qky_opportunity_ext table
  100. 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) 
  101. VALUES (@opportunityid,@userid,GetDate(),@userid,GetDate(),@sodate,@assignedaccountid,@notes,@shipaddress1,@shipaddress2,@shipaddress3,@shipcity,@shipstate,@shipzip,@shipaddress4,@shipcountry,@ship_name,@soshipdate,'Mac File')
  102.  
  103. SELECT @err = @@error
  104. IF @err = 0 
  105.   BEGIN
  106.   IF @@trancount>0 COMMIT TRANSACTION
  107.   END
  108. ELSE
  109.   BEGIN
  110.   IF @@trancount>0 ROLLBACK TRANSACTION
  111.   SET @returnvalue=1
  112.   RETURN @returnvalue
  113.   END
  114.  
  115.  
  116. --select the contact id
  117. SELECT @contactid = CONTACTID 
  118. FROM sysdba.CONTACT 
  119. WHERE FIRSTNAME=@firstname AND LASTNAME=@lastname AND ACCOUNTID=@accountid
  120.  
  121. --insert contact into opportunity_contact table if available
  122. IF (@contactid IS NOT NULL)
  123. INSERT INTO sysdba.OPPORTUNITY_CONTACT (OPPCONTACTID,CONTACTID,OPPORTUNITYID,CREATEUSER,CREATEDATE,MODIFYUSER,MODIFYDATE) 
  124. VALUES (@oppcontactid,@contactid,@opportunityid,@userid,GetDate(),@userid,GetDate())
  125.  
  126. SELECT @err = @@error
  127. IF @err = 0 
  128.   BEGIN
  129.   IF @@trancount>0 COMMIT TRANSACTION
  130.   END
  131. ELSE
  132.   BEGIN
  133.   IF @@trancount>0 ROLLBACK TRANSACTION
  134.   SET @returnvalue=1
  135.   RETURN @returnvalue
  136.   END
  137.  
  138.  
  139.  
  140. --insert values into the attachment table
  141. INSERT INTO sysdba.ATTACHMENT (ATTACHID, ATTACHDATE, ACCOUNTID, OPPORTUNITYID, DESCRIPTION, DATATYPE, FILESIZE, FILENAME, USERID)
  142. VALUES (@attachid,GetDate(),@accountid,@opportunityid,@description,'R',@filesize,@filename,@userid)
  143.  
  144. SELECT @err = @@error
  145. IF @err = 0 
  146.   BEGIN
  147.   IF @@trancount>0 COMMIT TRANSACTION
  148.   END
  149. ELSE
  150.   BEGIN
  151.   IF @@trancount>0 ROLLBACK TRANSACTION
  152.   SET @returnvalue=1
  153.   RETURN @returnvalue
  154.   END
  155. SET @returnvalue=0
  156. RETURN @returnvalue
  157. END
  158.  
  159.  
  160. GO
Feb 26 '09 #5

Plater
Expert 5K+
P: 7,872
Well there appear to be a number of places where your returnvalue would never be set, but returned anyway.

Expand|Select|Wrap|Line Numbers
  1. IF @err = 0  
  2.   BEGIN 
  3.   IF @@trancount>0 COMMIT TRANSACTION 
  4.   END 
  5. ELSE 
  6.   BEGIN 
  7.   IF @@trancount>0 ROLLBACK TRANSACTION 
  8.   SET @returnvalue=1 
  9.   RETURN @returnvalue 
  10.   END 
  11.  
if @err != 0 and @@trancount is not greater then zero, returnvalue is never set
Feb 26 '09 #6

100+
P: 233
I thought that adding a second ELSE clause would catch those value, but the procedure continues to return -1.
Expand|Select|Wrap|Line Numbers
  1. SELECT @err = @@ERROR
  2. IF @err = 0  
  3.    BEGIN 
  4.    IF @@trancount>0 COMMIT TRANSACTION 
  5.    END 
  6. ELSE 
  7.    IF @@trancount>0 --ROLLBACK TRANSACTION
  8.      BEGIN
  9.      SET @returnvalue=1 
  10.      RETURN @returnvalue
  11.      ROLLBACK TRANSACTION
  12.      END
  13.    ELSE
  14.      BEGIN
  15.      SET @returnvalue=1 
  16.      RETURN @returnvalue
  17.      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?


Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE spCreateOpportunity
  2. (
  3. @opportunityid    char(12),
  4. @userid        char(12)
  5. )
  6. AS
  7. BEGIN
  8. RETURN 5
  9. END
  10. GO
Feb 26 '09 #7

Plater
Expert 5K+
P: 7,872
Yes, your last example should return a 5
Feb 26 '09 #8

100+
P: 233
@Plater
The second line was needed, the return value was never being assigned to int returnValue. I appreciate your help with this Plater.

Expand|Select|Wrap|Line Numbers
  1. cmd.ExecuteNonQuery();
  2. int returnValue = (int)returnParm.Value;
  3. Console.WriteLine("Return value: {0}", returnValue);
Feb 27 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.