Output parameter not returning value?

Output parameter not returning value?

Post by Dave Veenema » Mon, 05 Jan 2004 23:20:52



I'm having problems getting an MSDE stored procedure to return a value.
Here's the procedure:

----------------------------------------------------------------------------
----

ALTER PROCEDURE CreateLedgerAccount
 (





 )
AS

 INSERT INTO LedgerAccounts
  (
   LedgerAccountName, LedgerAccountType, ParentLedgerID, LedgerAccountStatus
  )
  VALUES
  (

  )


 RETURN

----------------------------------------------------------------------------
----

The INSERT statement executes fine, and the SET statement appears to work
fine when I step through the procedure (in VS.Net). But after the procedure
returns,

 
 
 

Output parameter not returning value?

Post by Narayana Vyas Kondredd » Mon, 05 Jan 2004 23:51:43


Most likely, it is the way your are calling the proc, that is causing the
problem.

Could you post your .NET code that is calling the stored procedure?

Also, your post looks incomplete, towards the end.--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm


I'm having problems getting an MSDE stored procedure to return a value.
Here's the procedure:

----------------------------------------------------------------------------
----

ALTER PROCEDURE CreateLedgerAccount
 (





 )
AS

 INSERT INTO LedgerAccounts
  (
   LedgerAccountName, LedgerAccountType, ParentLedgerID, LedgerAccountStatus
  )
  VALUES
  (

  )


 RETURN

----------------------------------------------------------------------------
----

The INSERT statement executes fine, and the SET statement appears to work
fine when I step through the procedure (in VS.Net). But after the procedure
returns,

 
 
 

Output parameter not returning value?

Post by Dave Veenema » Mon, 05 Jan 2004 23:54:55


Sorry-- left off the end of the message...

But after the procedure returns, The output parameter value does not reflect
the change made inside the procedure.

Any suggestions as to why my output parameter is not updating properly?
Thanks.

--
Dave Veeneman
Chicago

 
 
 

Output parameter not returning value?

Post by Dave Veenema » Mon, 05 Jan 2004 23:58:24


Quote:> Could you post your .NET code that is calling the stored procedure?

Here's the C# code that's calling the procedure. It uses the MS Data Access
Application Block:

-------------------------------------------------

 internal int CreateLedgerAccount(BusinessObjects.LedgerAccount newAccount)
 {
  // Set up param array for stored procedure
  SqlParameter[] sqlParams = new SqlParameter[5];

newAccount.LedgerAccountName);

(int)newAccount.LedgerAccountType);

newAccount.ParentLedger.LedgerID);

(int)newAccount.LedgerAccountStatus);

  sqlParams[4].Direction = ParameterDirection.Output;

  // Execute stored procedure 'CreateLedgerAccount' to create new account
  SqlHelper.ExecuteNonQuery(p_ConnectionString, "CreateLedgerAccount",
sqlParams);

  // Return output value from stored procedure
  int ledgerAccountID = (int)sqlParams[4].Value;
  return ledgerAccountID;
 }

-------------------------------------------------

 
 
 

Output parameter not returning value?

Post by Uri Diman » Tue, 06 Jan 2004 10:06:59


Dave
Here is an example but with VB.NET
http://support.microsoft.com/default.aspx?scid=kb;EN-US;308049


Quote:> Sorry-- left off the end of the message...

> But after the procedure returns, The output parameter value does not
reflect
> the change made inside the procedure.

> Any suggestions as to why my output parameter is not updating properly?
> Thanks.

> --
> Dave Veeneman
> Chicago

 
 
 

Output parameter not returning value?

Post by Dave Veenema » Wed, 07 Jan 2004 14:08:49


Thanks--that helps a lot. There is a C# version of the same article:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;310070

 
 
 

1. SQL Output Parameters not returning values when there is an error in the SQL not caught by @@error

Problem:  SQL stored procedure output parameters not returning values
when there is an error in the SQL and the error causes the sp to quit

I have a VB program that calls a stored procedure.  The following 4
parms are set as follows in the VB app:

Set adoParm = adoCmd.Createparameter("Return", adInteger,
adParamReturnValue, , 0)

Set adoParm = adoCmd.Createparameter("TableName", adVarChar,
adParamInputOutput,100, " ")

Set adoParm = adoCmd.Createparameter("FailSafe", adInteger,
adParamInputOutput, ,0)

Set adoParm = adoCmd.Createparameter("SQLparm", adVarChar,
adParamInputOutput,1000, " ")

The stored procedure has its parameters as follows:


follows:



INSERT (---rest of statement here---)


In the VB app, it get the values of the parameters after the stored
procedure is done executing as follows:

nReturn = adoCmd.Paramters("Return").Value
strTableName= adoCmd.Parameters("TableName").Value
nFailSafe = adoCmd.Parameters("FailSafe").Value
strSQLparm = adocmd.Parameters("SQLparm").Value


clean exit from the stored procedure) then all of the param values are
retrieved with the correct values in the vb app.
HOWEVER, IN THE CASE WHERE THERE WAS AN ERROR THAT CAUSED THE STORED

FOLLOWING HAPPENS:
nFailSafe has the correct value.
strTableName AND strSQLparm are blank.  These values are not returned.
Does anyone know why?  The integer parameter (Failsafe) works fine but
the varchars do not.  How can this be fixed?

2. Update Joins/"Unique Table" does not work??

3. Stored proc not returning values with output parameters

4. 2.6 Report Variables Question

5. Stored procedure returning empty output parameters or return value

6. Return result set and return value/output parameter

7. How to capture the return value and output parameter value of a stored procedure in VB

8. Return values/output parameters from an anonymous block of t-sql

9. Returning value of COMPUTE as OUTPUT parameter

10. JDBC: return values and output parameters from stored procedures

11. Can't pass return value or output parameter to ADO if storeproc updates