How do i retrieve a Return Value (0 or 1) from my stored proc?

[Learner]

Hi there,
I have a storec proc that schedules a Sql job and finally it returns
0 then it was successfull and if it returns 1 then its unsuccessful.


Now when i run the stored proc in the query analyzer its returning

SQLServerAgent is not currently running so it cannot be notified of
this action.
Stored Procedure: aisinspect.dbo.SP_ChangeInspectionDateNew
Return Code = 0

So in my code behind page i am trying to retrieve the restult with the
below line of code.

Result = cmd.ExecuteScalar.ToString

But its throwing "Object reference not set to an instance of an
object."

I know that ExectureSclara returns the first row first column value.
But my stored proc doesn't return as a row column result its simply
displyaing the above text.


So i am just wondering any other method of cmd object that return the
above sort of values? or i do need to change my stored proc just to
return the result in a row (either 0 or 1) so that i can use
Executescalar in my codebehind page?

Thanks
-L

 

[Karl Seguin]

If you are using hte return keyword in in sproc, executeScalar won't work.
Try SELECT 1 and SELECT 0

also is ExecuteScalar() not ExecuteScalar (paranthesis)

Karl

 

[Ben Dewey]

If you are using return on the SP use:

SqlParameter ret = new SqlParameter("@RETURN_VALUE", returnValue);
ret.Direction = ParameterDirection.ReturnValue;
cmd.Parameteres.Add(ret);

// Get Data from Database
int numRows = cmd.ExecuteNonQuery();

this._id = int.Parse(ret.Value.ToString());

 

[Bruce Barker]

the retun value comes back as a parameter

try:

SqlParameter returnValue = new
SqlParameter("retuenValue",SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(returnValue)
cmd.ExecuteNonQuery();

int theReturnValue = (int) returnValue.Value;

or change your sql to

set nocount on
declare @r int
exec @r = myproc
select @r as returnValue

than executescaler will work


-- bruce (sqlwork.com)

 

[bhawin13]

Hello there,

You can do one thing design stored procedure with one out parameter.
Then in that procedure you assign your result.

From your asp.net code execute that procedure before executeNonQuery

define a parameter with out property.
Then executeNonQuery
Then retrive value of parameter.

B