Procedure returning empty output parameter

Procedure returning empty output parameter

Post by Cesar Ronches » Fri, 24 Oct 2003 21:06:42



Hi,

I have a procedure that executes an UPDATE and after, some output variables
are set with determined values.

The problem is, after the UPDATE statment, all following variables are
reseteds to null value!
If I remove/comment the UPDATE code, the output variables are returned OK to
my application (uses ADO 2.5/2.6, via command object).

Anyone knows happen?

The code is look like this:


AS

(...)

-- get resultset

--set the variables


Tks in advance!

Cesar

 
 
 

Procedure returning empty output parameter

Post by oj » Fri, 24 Oct 2003 23:12:32


Cesar,

1. You want to add 'set nocount on' to the begining of the procedure.
2. It's by design that SQL Server does not send back the values for the
return code or output parameters until the end of all result sets for the
procedure. So, you want to adjust your code accordingly.
3. I do not see the update code. You might want to post a complete text so
we can see.

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net


> Hi,

> I have a procedure that executes an UPDATE and after, some output
variables
> are set with determined values.

> The problem is, after the UPDATE statment, all following variables are
> reseteds to null value!
> If I remove/comment the UPDATE code, the output variables are returned OK
to
> my application (uses ADO 2.5/2.6, via command object).

> Anyone knows happen?

> The code is look like this:

> CREATE PROCEDURE spsml_GetDocument


> AS

> (...)

> -- get resultset

> --set the variables


> Tks in advance!

> Cesar


 
 
 

Procedure returning empty output parameter

Post by Cesar Ronches » Sat, 25 Oct 2003 18:20:23


Hi,

I did not posted the entire code because I was think no relevant.

But, I use another way to return my responses, through

My code already so changed!

Tks for response.


Cesar,

1. You want to add 'set nocount on' to the begining of the procedure.
2. It's by design that SQL Server does not send back the values for the
return code or output parameters until the end of all result sets for the
procedure. So, you want to adjust your code accordingly.
3. I do not see the update code. You might want to post a complete text so
we can see.

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net


> Hi,

> I have a procedure that executes an UPDATE and after, some output
variables
> are set with determined values.

> The problem is, after the UPDATE statment, all following variables are
> reseteds to null value!
> If I remove/comment the UPDATE code, the output variables are returned OK
to
> my application (uses ADO 2.5/2.6, via command object).

> Anyone knows happen?

> The code is look like this:

> CREATE PROCEDURE spsml_GetDocument


> AS

> (...)

> -- get resultset

> --set the variables


> Tks in advance!

> Cesar

 
 
 

1. Stored procedure returning empty output parameters or return value

I have a stored procedure that when passed a username I want to call
sp_droplogin. If this call fails (user or aliased in a database)
return error value -1. If successful return 1.

I can get it to work using sql oledb whether it fails or not, but not
using a ODBC DSN. If it is successful I get 1 returned if it fails I
get nothing at all. I have to use the DSN approach so I need to figure
out what's going on.

I've tried using both a output parameter and a return value with the
same results.

I'm developing in VB 6, SQL 7, MDAC 2.6

Also, even when I set a default value for the output parameter/return
value before I call sp_droplogin, it gets wiped out and nothing is
returned.

Any help would be appreciated.

Steve

2. INET application is WAN friendly ???

3. Why do output parameters return as empty?

4. Not happy together: COUNT(*) and COUNT(DISTINCT ...)

5. Empty output parameters when executing stored procedure through ODBC

6. Question on Extents

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

8. INGRES... POSTGRES, etc.

9. Returning output parameter from Stored Procedure

10. Help! Stored Procedure Return/Output Parameters

11. Getting OUTPUT parameters and return values from Stored Procedures

12. Returning both recordset and output parameters into VB from stored procedure

13. Stored procedure not returning output parameter