p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

asp_databases thread: reusing a local variable used in a stored procedure in the rest of an asp page..


Message #1 by "Eric Van Camp" <eric.vancamp@c...> on Sat, 9 Dec 2000 15:15:16 -0000
i have a variable called coyid in my stored procedure, which i want to use

in my asp page.

When the uses accesses the page, the stored procedure is excuted, checking

his company and hthe permissions of the user.

The stored procedure is closed.

The variable coyid only exists as a local variable in the stored procedure,

now i want to use the value of this variable in the rest of my page.

What is the best method to do this?

txs

eric



Message #2 by "Dave Sussman" <davids@i...> on Sun, 10 Dec 2000 11:55:15 -0000
You need to either return the variable from the stored procedure, either as

a return value or an output parameter. For a return value you'd do this:



CREATE PROCEDURE usp_Foo

AS

  Select ....



  RETURN @local_variable



Then in your asp page:



Set cmd = Server.CreateObject("ADODB.Command")

cmd.CommandType = adCmdStoredProc

cmd.CommandText = "usp_Foo"

cmd.Parameters.Append cmd.CreateParameter("RETURN_VALUE", adInteger,

adParamReturnValue)

cmd.Execute

ASPVariable = cmd.Parameters("RETURN_VALUE")





Alternatively, you can use an output parameter:



CREATE PROCEDURE usp_Foo

  @RV int

AS

  Select ....



  select @RV = @local_variable



And then in your code:



Set cmd = Server.CreateObject("ADODB.Command")

cmd.CommandType = adCmdStoredProc

cmd.CommandText = "usp_Foo"

cmd.Parameters.Append cmd.CreateParameter("@RV", adInteger, adParamOutput)

cmd.Execute

ASPVariable = cmd.Parameters("@RV")



Dave





"Eric Van Camp" <eric.vancamp@c...> wrote in message

news:24226@a..._databases...

>

> i have a variable called coyid in my stored procedure, which i want to use

> in my asp page.

> When the uses accesses the page, the stored procedure is excuted, checking

> his company and hthe permissions of the user.

> The stored procedure is closed.

> The variable coyid only exists as a local variable in the stored

procedure,

> now i want to use the value of this variable in the rest of my page.

> What is the best method to do this?

> txs

> eric

>

>

>






  Return to Index