Okay. I usually like to segregate my stored procedures between those that do queries and those that update, insert, delete data. If a stored proc that does queries returns an error I usually do that by raising an error in the procedure.
Thanks, Mark Potter -----Original Message----- From: Kevin Hegg [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 18, 2004 10:32 AM To: [EMAIL PROTECTED] Subject: Re: [ADVANCED-DOTNET] Stor proc return values and DataReader Unfortunately, you are solving a different problem than the original author asked about. He wanted to know if it was possible to get the return value before the result set when there is a result set. You showed him how to get the return value when there is no result set. Kamen's answer is correct when one or more result sets are being returned. -----Original Message----- From: Moderated discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Potter, Mark S. Sent: Wednesday, February 18, 2004 8:59 AM To: [EMAIL PROTECTED] Subject: Re: [ADVANCED-DOTNET] Stor proc return values and DataReader /* code snippet to get retval from stored proc */ SqlConnection sqlConn = new SqlConnection("server=(local);database=MyDB;uid=sa;pwd=;"); SqlCommand cmd = new SqlCommand("StoredProc", sqlConn); /* CREATE PROCEDURE dbo.StoredProc @FloatArg1 float, @IntArg2 int, @VarcharArg3 varchar(10) AS declare @Retval int set @Retval = 1 .. .. return @Retval */ SqlParameter param = null; SqlParameter paramRet = null; cmd.CommandType = CommandType.StoredProcedure; /* ret val */ paramRet = cmd.Parameters.Add("@Retval", SqlDbType.Int, 0); paramRet.Direction = ParameterDirection.ReturnValue; /* float val */ param = cmd.Parameters.Add("@FloatArg1", SqlDbType.Float, 0); param.Value = 1.25; /* int val */ param = cmd.Parameters.Add("@IntArg2", SqlDbType.Int, 0); param.Value = 2; /* varchar val */ string s = "abcdefg"; /* throw an exception if len of s > 10 */ param = cmd.Parameters.Add("@VarcharArg3", SqlDbType.VarChar, s.Length); param.Value = sVal; Object o = cmd.ExecuteScalar(); int nRet = (int) paramRet.Value; -----Original Message----- From: Kamen Lilov [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 17, 2004 1:02 PM To: [EMAIL PROTECTED] Subject: Re: [ADVANCED-DOTNET] Stor proc return values and DataReader Unfortunately, no. Output parameters (which includes the result given on RETURN) are always sent by SQL Server _after_ all resultsets (these may be more than once) have been sent and received by the consumer. It is a deficiency - or call it a quirk, if you want - of the inner workings of SQL Server and its wire-level protocol, not of any particular client library or data access wrapper. AFAIK there is no universal way to deal with this. What's more, you have to be careful of the number of resultsets your SP returns - these might be 0, 1 or more, depending on when your RETURN statement executed. You have to walk forward through all rows in these resultsets before you get access to any output params. -----Original Message----- From: Moderated discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Alex Ivanoff Sent: Tuesday, February 17, 2004 7:16 PM To: [EMAIL PROTECTED] Subject: [ADVANCED-DOTNET] Stor proc return values and DataReader Lets say I have a stor proc like this: CREATE PROCEDURE spDo AS SET NOCOUNT ON SELECT name FROM Author IF (0<>@@ERROR) RETURN @@ERROR IF (2 > @@ROWCOUNT) RETURN (-1) GO In the code I have something like this: SqlCommand cmd = new SqlCommand(); ... SqlDataReader reader = cmd.ExecuteReader(); This is where the problem begins. In order to get return value from stor proc I have to close the reader. Basically, I read all the data, close reader, find out that errors occurred and throw the data away. It does not seem to be right. Is there a better approach to this? Thank you, Alex Ivanoff =================================== This list is hosted by DevelopMentor� http://www.develop.com Some .NET courses you may be interested in: NEW! Guerrilla ASP.NET, 17 May 2004, in Los Angeles http://www.develop.com/courses/gaspdotnetls View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor� http://www.develop.com Some .NET courses you may be interested in: NEW! Guerrilla ASP.NET, 17 May 2004, in Los Angeles http://www.develop.com/courses/gaspdotnetls View archives and manage your subscription(s) at http://discuss.develop.com
