Geeks With Blogs
New Things I Learned

Recently we changed our DAC layer from using inline SQL to stored procedures in the database. On some of these SQL, we did record deletion (usually only 1 record), and we just execute it via IDBCommand.ExecuteNonQuery() and then check the return value to see how many records were affected (which should be 1) for verification that the query actually does something.

With the change to stored procedure, we just return 1 in the stored procedure if the delete is successful. However, the calling code then started to show these deletions as errors. Apparently ExecuteNonQuery only returns the number of affected rows on SELECT, INSERT and DELETE statements; for everything else it returns -1.  So I tried to figure out how to get a return value from a stored procedure.

Let's assume a simplistic stored procedure as follows:

ALTER PROC ReturnOnly
AS
BEGIN
      RETURN 5
END

You can't use ExecuteScalar to get the returned value, and ExecuteNonQuery will always return -1.  To get the value back, you need to add a return value parameter to the command.  The name of the parameter is not important.  The code to get the value returned by that procedure will be as follows:

private int ExecuteStoredProcedure(string storedProcedureName)
{
   SqlConnection connection = new SqlConnection(connectionString);
 
   // Command - specify as StoredProcedure
   SqlCommand command = new SqlCommand(storedProcedureName, connection);
   command.CommandType = CommandType.StoredProcedure;
 
   // Return value as parameter
   SqlParameter returnValue = new SqlParameter("returnVal", SqlDbType.Int);
   returnValue.Direction = ParameterDirection.ReturnValue;
   command.Parameters.Add(returnValue);
 
    // Execute the stored procedure
   connection.Open();
   command.ExecuteNonQuery();
   connection.Close();
 
   return Convert.ToInt32(returnValue.Value);
}

 

Posted on Wednesday, September 17, 2008 11:57 AM .NET , SQL | Back to top


Comments on this post: Get return value from stored procedure

# re: Get return value from stored procedure
Requesting Gravatar...
There are much more detailed samples and explantions in the help file of the Data Access Library from MS.
Left by Saif Khan on Dec 22, 2008 8:21 PM

# re: Get return value from stored procedure
Requesting Gravatar...
The detailed samples and explanations aren't nearly as clear and straightforward as this. Those writers need to understand how to add code that is relevant to the solution; far too often, those samples add extraneous code which obfusticates the whole exercise.
Left by billy on Mar 29, 2009 11:47 PM

# re: Get return value from stored procedure
Requesting Gravatar...
ALTER PROC ReturnOnly
AS
BEGIN
SELECT 5
END
Left by Yclept on Jun 11, 2009 7:27 AM

# re: Get return value from stored procedure
Requesting Gravatar...
Thanks a lot buddy!

I have spend my 4 hours to find the solution. I found it here.

Why I could not be able to find u in the morning.

Thanks

Ravi Goswami
Left by Ravi Goswami on Jun 18, 2009 11:17 AM

# re: Get return value from stored procedure
Requesting Gravatar...
I love you. I wasted the past few hours trying to find this. It solved my problem. Wish Google would direct me here earlier.

Good Job.
Left by Li on Jun 25, 2009 3:29 PM

# re: Get return value from stored procedure
Requesting Gravatar...
Thanks a lot, really usefull.
Left by Eternal Bard on Aug 12, 2009 12:34 PM

# re: Get return value from stored procedure
Requesting Gravatar...
Thank you very much for your solution! It works really.
Left by Dimitrij Albocha on Aug 31, 2009 9:39 PM

# re: Get return value from stored procedure
Requesting Gravatar...
code helps me lot !!! thanks.
Left by harshada on Nov 05, 2009 12:25 AM

# re: Get return value from stored procedure
Requesting Gravatar...
finally someone who makes it clear!
thanks
Left by andre on Dec 05, 2009 10:47 PM

# re: Get return value from stored procedure
Requesting Gravatar...
This looks like the answer to a problem that has been way to hard to solve. I am using exec in a stored procedure to run parameter driven SQL just to get a record count. Do you have the equivalent in VB?
Left by GaryHumf on Feb 15, 2010 4:25 PM

# re: Get return value from stored procedure
Requesting Gravatar...
can someone tell me how to grab multiple rows returned by sproc?
Left by Pruthvi on Feb 25, 2010 10:14 PM

# thanks
Requesting Gravatar...
this code helps me lotssss
thanks
Left by heni on Apr 20, 2010 9:48 PM

# re: Get return value from stored procedure
Requesting Gravatar...
thanks dear
Left by vinay on Apr 26, 2010 8:40 PM

# re: Get return value from stored procedure
Requesting Gravatar...
thanks a lot buddy
Left by sam on Jun 02, 2010 6:00 AM

# re: Get return value from stored procedure
Requesting Gravatar...
Good and Clear Example. Thanks a lot.
Left by krutarthp on Aug 12, 2010 1:20 AM

# re: Get return value from stored procedure
Requesting Gravatar...
Hey man, this is great. Exactly what I've been looking for.
Left by PainBringer on Nov 17, 2010 12:35 AM

# re: Get return value from stored procedure
Requesting Gravatar...
thnx a lot...
this code really helps
Left by ankit garg on Nov 27, 2010 9:18 PM

# re: Get return value from stored procedure
Requesting Gravatar...
Thank u very much.

This code very useful to me
Left by Rajalingam on Dec 15, 2010 7:25 PM

# re: Get return value from stored procedure
Requesting Gravatar...
"Righteous! RIGHTEOUS!!"
--Crush "Finding Nemo"
Left by wardeaux on Jan 28, 2011 1:59 AM

# re: Get return value from stored procedure
Requesting Gravatar...
I Love You. This Solve Problem Very Fast
Left by Hemant Ghaydar on Mar 21, 2011 9:49 PM

# Thanks
Requesting Gravatar...
Thnaks Nice & Neat
and above all simple & clear
Left by ankit on Sep 04, 2011 12:47 AM

# re: Get return value from stored procedure
Requesting Gravatar...
where put the return value?
Left by Goku on Sep 07, 2011 7:33 AM

# re: Get return value from stored procedure
Requesting Gravatar...
Thanks a lot, I spend almost 2 hrs to find the solution...
Left by sudhir on Sep 14, 2011 7:25 AM

# re: Get return value from stored procedure
Requesting Gravatar...
Thanks Dude
Left by Abby on Dec 06, 2011 4:38 AM

# re: Get return value from stored procedure
Requesting Gravatar...
Thanx Dude.Realy simple to understand but yet affectf!!
Left by Hanno on Dec 07, 2011 11:02 PM

# Thank You very much
Requesting Gravatar...
you help me , I need to know this solution , tnx again .
Left by babak on Dec 18, 2011 9:27 PM

# re: Get return value from stored procedure
Requesting Gravatar...
Thanks yar i spend a lots of time for this after seeing your post i found my mistake....
Left by Murali on Jan 19, 2012 6:15 PM

# re: Get return value from stored procedure
Requesting Gravatar...
Hi,

Thanks for such a nice post, really helped me. Can you please tell us how to get the multiple return values?
Please assist.

Looking for your sooner reply,
Thanks in Advance !!

Sunny


Left by Sunny on Feb 05, 2012 9:22 PM

# re: Get return value from stored procedure
Requesting Gravatar...
Thank you so much. How to create a stored procedure that return a multiple values. Thanks in advance
Left by kenneth on Feb 26, 2012 2:34 PM

# re: Get return value from stored procedure
Requesting Gravatar...
Nice tutorial, how to get all views in a specific database.

Left by Cebu City Jobs on Mar 29, 2012 3:21 AM

# re: Get return value from stored procedure
Requesting Gravatar...
Straight to the point. Thanks!
Left by Mohamed on Jun 12, 2012 2:31 AM

# re: Get return value from stored procedure
Requesting Gravatar...
Thank you so much.... i have solved. Its really helpful.. I love you
Left by Kishi on Jun 25, 2012 12:17 AM

# re: Get return value from stored procedure
Requesting Gravatar...
Nice info..thanks for sharing..!!
Left by Harshita on Jul 14, 2012 8:24 PM

# re: Get return value from stored procedure
Requesting Gravatar...
Awesome, thanks!
Left by Dan on Feb 20, 2013 4:15 AM

# re: Get return value from stored procedure
Requesting Gravatar...
that's great! thanxxxx
Left by cyrus on May 22, 2013 10:26 PM

# re: Get return value from stored procedure
Requesting Gravatar...
I've searched for this all over the internet but only crappy answers.
Thx a lot!!!
Left by darko on Aug 01, 2013 2:07 AM

# re: Get return value from stored procedure
Requesting Gravatar...
quite cool answer , i searched about an hr, but d solution above is appropriate one .
Left by anusha K on Mar 11, 2014 8:07 PM

# re: Get return value from stored procedure Linq
Requesting Gravatar...
Hi,
How can we solve the same problem with linq
Left by ada on Dec 03, 2017 11:44 PM

Your comment:
 (will show your gravatar)


Copyright © Muljadi Budiman | Powered by: GeeksWithBlogs.net