C# Stored Procs

niceguyeddie1999

Hi, I have a stored procedure and it looks like the following ALTER PROCEDURE USP_NCUInsertContentCodeTest @var1 int, @var2 int etc AS DECLARE @RC int //Do stuff RETURN @RC I know how to execute the procedure in c# but I cant figure out how to get the return value. I know if I declared the @RC before the AS I could declare it as a parameter with parameterdirectio = output etc but I cannot change the stored procedure. How can I do it? Thank you in advance, Ed

Xodiak

why not just do: Select @RC instead?
it will return a DataSet to c#...fill the DataSet using a sqladapter and then access the variable:
DataSet ds = new DataSet();
//Do stuff
int result = (int)ds.Tables[0].Row[0][0];

hope that helps

niceguyeddie1999

Sorry I dont know what you mean by do a select @RC

Colin Angus Mackay

Xodiak wrote:

it will return a DataSet to c#...fill the DataSet using a sqladapter and then access the variable:

:omg: That is so much overkill!

int result = (int)mySqlCommand.ExecuteScalar();

That is all you need. ColinMackay.net "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius "If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Colin Angus Mackay

He means that in your stored procedure, instead of using a return that you do a

SELECT @rc

If you don't SELECT anything else in the stored procedure then this can work quite well. In your C# application you would use ExecuteScalar() on the SqlCommand class in order to retrieve the result, like this:

SqlCommand cmd = new SqlCommand();
cmd.Connection = myConnection;
cmd.CommandText = "MyStoredProcName";
cmd.CommandType = CommandType.StoredProcedure;
int result = (int)cmd.ExecuteScalar()

Of course, if you are already returning other data from the stored procedure, this SELECT becomes a bit more difficult to manage. In that case you can retrieve the result by using a parameter to get the result of the RETURN statement. In your command object add this to the list of parameters:

SqlParameter returnParam= new SqlParameter("RETURN_VALUE",SqlDbType.Int);
returnParam.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(returnParam);

After you execute your stored procedure you can get the return value like this:

int result = (int)(cmd.Parameters["RETURN_VALUE"].Value)
// or, if returnParam is still in scope
int result = (int)returnParam.Value

Does this help? ColinMackay.net "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius "If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell -- modified at 13:30 Monday 23rd January, 2006

Xodiak

interesting, how would you handle multiple variables returned(kinda like an output)?

Guffa

Add a parameter with the direction ReturnValue. After the call it will contain the value. --- b { font-weight: normal; }

Dave Kreskowiak

Now you'd have to return those values as a dataset. Any stored procedure can only return one value using the return value method. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

Colin Angus Mackay

To follow up on what Dave said (and, if I may quote Scotty in Star Trek V) use "the right tool for the right job". You can also have a number of output parameters in the stored procedure. ColinMackay.net "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius "If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

niceguyeddie1999

Thats the one, thank you