Very Computer

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

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

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

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