Execute non query

[Nilay Shah]

Hi,

I am executing stored procedure (which is deleting the
records) with the help of Execute non query. While
passing the parameters, also have specified one parameter
as return parameter as.
param.Direction = ParameterDirection.ReturnValue;

In the stored procedure (DB SQL Server 2000) after delete
statement I have specified code as

delete from <table> where <condition>
if @@error <> 0
return -1
else
return 0

I was expecting that if delete fails then it would return -
1 else it would return 0.. but instead of that it is going
in exception.. I am not sure if this is the expected
behaviour of executenonquery.

If this is the case then I have a question that where
should I identify business error and exception.. as if
delete is failed then i would get exception and so is if
database is down.. so how to differentiate??

Kindly help.

Thanks and regards,
Nilay.

 

[Ziga Jakhel]

Nilay, create a SQL Command object with your sproc in a Windows Form and
take a look at it's parameter collection.

You will find that there is a default return value parameter @RETURN_VALUE,
which represents the return value of a sproc (the output of using "return
xy" in a sproc)

In the case of your sproc you are actually writing to this parameter, as you
are assigning value to the default return of the sproc. This approach should
be useful in most cases and replace the need for a custom return parameter.

If you, however, *really* want or need to use a custom return parameter, you
should:

1) Declare the parameter in the sproc as OUTPUT
e.g.
create procedure dbo.MyProcedure
(
@myparam1 int
, @returnparam varchar(16) OUTPUT <-- like this
)

2) When returning, first assign a value to your parameter, then call return.
Like:

delete from <table> where <condition>
if @@error <> 0
set @return_parameter = -1
return -1
else
set @return_parameter = 0
return 0

In this case the value included in the return statement is more or less
meaningless, unless you are, for instance, using it to return an error
message in a custom parameter and an error code in the return value.


Regards,

Ziga Jakhel, MCAD.NET
IUS SOFTWARE d.o.o.
www.ius-software.si

 

[Nilay Shah]

Didnt get your answer properly..

My Data Access Layer is as follows:

SqlConnection objConnection = new SqlConnection
(cConstants.CONN_STRING);
SqlCommand objCommand = new SqlCommand();
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Connection = objConnection;
objCommand.CommandText = "sp_adm_del_kit";

/*Populating SqlPArameters array.*/
SqlParameter objParam = new SqlParameter();
objParam = new SqlParameter("@IN_KitID", SqlDbType.Int);
objParam.Value = pintKitID;
objCommand.Parameters.Add(objParam);

objParam = new SqlParameter("@Ret_value", SqlDbType.Int);
objParam.Direction = ParameterDirection.ReturnValue;
objCommand.Parameters.Add(objParam);

objConnection.Open();
objCommand.ExecuteNonQuery();

if (@Ret_value == 0)
--- success
else
-- set error

My Stored Procedure is as follows:

CREATE PROCEDURE sp_kit_del_Kit
@IN_KitID int
AS
BEGIN
Delete from KitHeader where KitID=@IN_KitID
IF @@ERROR <> 0
RETURN -1
else
return 0

If I execute this code in data access layer, and if there
is some problem, as foreign key refernce and record cannot
be deleted, then it goes into exception and doesnt come to
next step after executenonquery.. Any reason why so? I was
assuming it would come to next line with value of
@ret_value as -1

as per ur answer, i need not specify return value.. can u
send me sample code..