Return Code from Stored Proc

I am doing an insert with a stored proc using the ExecuteNonQuery in the
DataAccess Block from Microsoft. My parameters are inserted correctly into
the database but my return code is always a -1 instead of 0. Please review
this code and tell me if you see something I am doing wrong> Thanks in
Advance!
CREATE PROCEDURE insertTrans_sp
(@.batch_id numeric,
@.cpi numeric,
@.visit numeric,
@.qty numeric,
@.gl_proc varchar(8),
@.gl_desc varchar(50),
@.charge float,
@.processed_date datetime,
@.processed_by varchar(50))
AS
SET NOCOUNT ON
DECLARE
@.err int,
@.err_desc varchar(50)
Begin Transaction
INSERT INTO Trans
(batch_id,
cpi,
visit,
qty,
gl_proc,
gl_desc,
charge,
processed_date,
processed_by)
VALUES
(@.batch_id,
@.cpi,
@.visit,
@.qty,
@.gl_proc,
@.gl_desc,
@.charge,
@.processed_date,
@.processed_by)
SET @.err = @.@.error
if @.err <> 0 GOTO ErrorHandler
COMMIT Transaction
return 0
ErrorHandler:
SET @.err_desc = 'Error occurred in insertTrans_sp'
EXEC insertErrLog_sp @.err, @.err_desc
return -100
GO
Robert HillRobert,
Exactly how are you executing this? Is this ADO, ADO.net etc? A couple of
comments here. One is that it is not necessary to wrap the Insert in an
explicit transaction. Any single sql statement is ATOMIC by itself. The
insert will either succeed or it won't. By wrapping it in a tran you now
have to commit or roll it back yourself. In this case you don't even have
any code to issue a rollback. You should always test the trancount level
before issuing a commit or rollback.
IF @.@.TRANCOUNT > 0
COMMIT TRAN
You declare a series of variables as Numeric but do not specify their
precision or scale. Always specify the size or scale of all data types.
These all look like Integers anyway. If that is the case it is more
efficient to declare them as integers than numeric.
Andrew J. Kelly SQL MVP
"Robert" <rhill938@.hotmail.com> wrote in message
news:0B27280E-35B3-4548-B928-34A0E227453F@.microsoft.com...
>I am doing an insert with a stored proc using the ExecuteNonQuery in the
> DataAccess Block from Microsoft. My parameters are inserted correctly
> into
> the database but my return code is always a -1 instead of 0. Please
> review
> this code and tell me if you see something I am doing wrong> Thanks in
> Advance!
> CREATE PROCEDURE insertTrans_sp
> (@.batch_id numeric,
> @.cpi numeric,
> @.visit numeric,
> @.qty numeric,
> @.gl_proc varchar(8),
> @.gl_desc varchar(50),
> @.charge float,
> @.processed_date datetime,
> @.processed_by varchar(50))
> AS
> SET NOCOUNT ON
> DECLARE
> @.err int,
> @.err_desc varchar(50)
> Begin Transaction
> INSERT INTO Trans
> (batch_id,
> cpi,
> visit,
> qty,
> gl_proc,
> gl_desc,
> charge,
> processed_date,
> processed_by)
> VALUES
> (@.batch_id,
> @.cpi,
> @.visit,
> @.qty,
> @.gl_proc,
> @.gl_desc,
> @.charge,
> @.processed_date,
> @.processed_by)
> SET @.err = @.@.error
> if @.err <> 0 GOTO ErrorHandler
> COMMIT Transaction
> return 0
> ErrorHandler:
> SET @.err_desc = 'Error occurred in insertTrans_sp'
> EXEC insertErrLog_sp @.err, @.err_desc
> return -100
> GO
> --
> Robert Hill
>|||in order to get a return code, I believe you need to use execute Scalar
Greg Jackson
PDX, Oregon|||"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eAujW9fHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> in order to get a return code, I believe you need to use execute Scalar
>
Yes, the return from ExecuteNonQuery is NOT the stored procedure return
code.
No ExecuteScalar won't help. To get the return code you will need to use
CommandType.Text and write a batch like:
exec @.rc=MyProc(@.p1,@.p2,@.p3)
then bind an output parameter to @.rc.
In your case it's not necessary to test the return code. From client code
if something goes wrong you will get a SqlException. A stored procedure
return code is really just for other stored procedures. When one procedure
calls another procedure the calling procedure cannot intercept the error
messages generated by the called procedure, so it must use the return code
to determine if something went wrong. From SqlClient the error message will
appear as a SqlException and you can examine it in your catch block.
David|||Thanks.
"David Browne" wrote:

> "pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
> news:eAujW9fHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Yes, the return from ExecuteNonQuery is NOT the stored procedure return
> code.
> No ExecuteScalar won't help. To get the return code you will need to use
> CommandType.Text and write a batch like:
> exec @.rc=MyProc(@.p1,@.p2,@.p3)
> then bind an output parameter to @.rc.
> In your case it's not necessary to test the return code. From client code
> if something goes wrong you will get a SqlException. A stored procedure
> return code is really just for other stored procedures. When one procedur
e
> calls another procedure the calling procedure cannot intercept the error
> messages generated by the called procedure, so it must use the return code
> to determine if something went wrong. From SqlClient the error message wi
ll
> appear as a SqlException and you can examine it in your catch block.
> David
>
>|||Robert,
you dont have to set the command type to text.
you can (And in my opinion, should) leave the command type to "stored
procedure"
you can create a parameter object with direction of "Output" and get the
return value.
GAJ|||Thanks.
My solution was to use ExecuteScalar in the Application Block with the
command type "stored procedure" with an output parameter.
Robert
"pdxJaxon" wrote:

> Robert,
> you dont have to set the command type to text.
> you can (And in my opinion, should) leave the command type to "stored
> procedure"
> you can create a parameter object with direction of "Output" and get the
> return value.
> GAJ
>
>|||pdxJaxon wrote:
> Robert,
> you dont have to set the command type to text.
> you can (And in my opinion, should) leave the command type to "stored
> procedure"
> you can create a parameter object with direction of "Output" and get
> the return value.
>
You can also use a parameter with direction of "ReturnValue" to get the
return value ...
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Thanks!
I now need to return 2 values from a table. I tested my stored proc in
query analyzer and it seemed to work fine, returning the values I need. The
foloowing is the code I use in my app to call the stored proc using a
SqlParameter array. All I get back is a -1. What am I doing wrong?
SqlParameter[] oParms = new SqlParameter[3];
try
{
oParms[0] = new SqlParameter("@.cpi", sCPI);
oParms[1] = new SqlParameter("@.visit", ParameterDirection.Output);
oParms[2] = new SqlParameter("@.batch_id", ParameterDirection.Output);
ConnectString oCn = new ConnectString();
cn = oCn.GetConnection();
object oRes = new object();
oRes = SqlHelper.ExecuteNonQuery(cn,
CommandType.StoredProcedure,
"verifyCPIandBatchId_sp",
oParms);
Robert
"Bob Barrows [MVP]" wrote:

> pdxJaxon wrote:
> You can also use a parameter with direction of "ReturnValue" to get the
> return value ...
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>|||close the connection before attempting to read the output parms
GAJ