Return value from SPs

Return value from SPs

Post by Archimede D'Agostin » Sat, 10 Nov 2001 02:06:05



Hi, This is driving me crazy....I can't get a return value from a Stored
Procedure.

I've made a simple test SP
----------------------------------------------
CREATE PROCEDURE [spTest]
AS
return 1
----------------------------------------------
but when I call it from this ASP code
----------------------------------------------
...omissis....

oCmd.CommandText = "spTest"
oCmd.CommandType = adCmdStoredProc

Set oRs = oCmd.Execute

Response.Write "1 - " & oCmd.Parameters(i).Direction & "<BR>"
Response.Write "2 - " & oCmd.Parameters(i).Name & "<BR>"
Response.Write "3 - " & oCmd.Parameters(i).Type & "<BR>"
Response.Write "4 - " & Cint(oCmd.Parameters(i).Value) & "<BR>"

...omissis....
----------------------------------------------
I get this
----------------------------------------------
1 - 4
2 - RETURN_VALUE
3 - 3
4 - 0

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

Can anybody help me?
Thanks

Archimede D'Agostino

 
 
 

Return value from SPs

Post by Nick Dawso » Sat, 10 Nov 2001 03:00:26


Look at OUTPUT parameters in an SP.


as

begin


end





Quote:> Hi, This is driving me crazy....I can't get a return value from a Stored
> Procedure.

> I've made a simple test SP
> ----------------------------------------------
> CREATE PROCEDURE [spTest]
> AS
> return 1
> ----------------------------------------------
> but when I call it from this ASP code
> ----------------------------------------------
> ...omissis....

> oCmd.CommandText = "spTest"
> oCmd.CommandType = adCmdStoredProc

> Set oRs = oCmd.Execute

> Response.Write "1 - " & oCmd.Parameters(i).Direction & "<BR>"
> Response.Write "2 - " & oCmd.Parameters(i).Name & "<BR>"
> Response.Write "3 - " & oCmd.Parameters(i).Type & "<BR>"
> Response.Write "4 - " & Cint(oCmd.Parameters(i).Value) & "<BR>"

> ...omissis....
> ----------------------------------------------
> I get this
> ----------------------------------------------
> 1 - 4
> 2 - RETURN_VALUE
> 3 - 3
> 4 - 0

> ----------------------------------------------

> Can anybody help me?
> Thanks

> Archimede D'Agostino


 
 
 

Return value from SPs

Post by Ion Chalmers Freema » Sat, 10 Nov 2001 03:14:32


Archimede,
        You may be having a problem with ADO -- does this work?
<code>
create procedure plunk as return 1
go



go
drop procedure plunk
</code>
        Look at your connection's properties. How do you get the command to understand that it has parameters? Are you using
parameters.refresh?
ion

> Hi, This is driving me crazy....I can't get a return value from a Stored
> Procedure.
> but when I call it from this ASP code
> ----------------------------------------------

> oCmd.CommandText = "spTest"
> oCmd.CommandType = adCmdStoredProc

> Set oRs = oCmd.Execute
> Response.Write "4 - " & Cint(oCmd.Parameters(i).Value) & "<BR>"

> 4 - 0

> Archimede D'Agostino

 
 
 

Return value from SPs

Post by Archimede D'Agostin » Sat, 10 Nov 2001 03:25:24


I've noticed that calling the SP with

..... Set oRs = oCmd.Execute

doesn't return anything while using

.... oCmd.Execute

it works!! Can you tell me why?
Can't I get return values if the SP return a recordset??

Thanks
Archimede D'Agostino



Quote:> Hi, This is driving me crazy....I can't get a return value from a Stored
> Procedure.

> I've made a simple test SP
> ----------------------------------------------
> CREATE PROCEDURE [spTest]
> AS
> return 1
> ----------------------------------------------
> but when I call it from this ASP code
> ----------------------------------------------
> ...omissis....

> oCmd.CommandText = "spTest"
> oCmd.CommandType = adCmdStoredProc

> Set oRs = oCmd.Execute

> Response.Write "1 - " & oCmd.Parameters(i).Direction & "<BR>"
> Response.Write "2 - " & oCmd.Parameters(i).Name & "<BR>"
> Response.Write "3 - " & oCmd.Parameters(i).Type & "<BR>"
> Response.Write "4 - " & Cint(oCmd.Parameters(i).Value) & "<BR>"

> ...omissis....
> ----------------------------------------------
> I get this
> ----------------------------------------------
> 1 - 4
> 2 - RETURN_VALUE
> 3 - 3
> 4 - 0

> ----------------------------------------------

> Can anybody help me?
> Thanks

> Archimede D'Agostino

 
 
 

1. Two Process Instances Using SQL 6.5 SPs Return Different Values

This is a similar to a problem that I reported about four weeks ago.

Under SQL Server 6.5/SP5a on an Alpha running NT 4.0 Service Pack 4. If I
execute two instances of a MS C console program at the same time, which
extract their
data from complex read only SQL Server Stored Procedure Cursors via ODBC 3,
sometimes
the data is the same, sometimes not   :-(

It also cannot be predicted when the data will not match. If the program is
executed, multiple times in a sequential fashion, this is not a problem, the
output always matches. Note, that when the outputs do not match, neither
match the output that comes from any of the good concurrent or sequential
executions.

Coming from a mainframe environment, is there something obvious that I'm
missing here ? Is their some extra locking that I have to control. My only
hint so far,
is that the fields that appear to differ are subject to the CONVERT
statement
of a DECIMAL field to a SMALLINT field egCONVERT(SMALLINT, weight * 100).
Does SQL Server 6.5 have some thread safety problems ?

Regards
Pat Harms

2. TCPIP error SQL30081

3. Issue with SPs returning name-value pairs

4. Foreign Key Issues

5. Form refresh problem

6. Nested SPs and Commit on Return

7. Deffred prepare could not be completed?

8. Return code from SPs

9. Passing and returning Arrays to SPs

10. ESQL/C calling SPs - seg fault with RETURN from EXCEPTION block

11. Retrieving SQL Server SPs Return Recordset Column Names

12. Seeking advice on returning large data chunks from SPs