View Single Post
  #1 (permalink)  
Old March 30th, 2005, 11:07 AM
rweide rweide is offline
Authorized User
 
Join Date: Sep 2004
Location: , , USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default Use Temp Table Variable in Stored Procedure

I'm playing with simple stored procedure with declared table variable as below:

Code:
CREATE PROCEDURE TEST
AS 
    Declare @myTable table
    (    
    Company varchar(10),
        Amount Money
    )
    INSERT INTO @myTable (Company, Amount)
    select company, amount from tblmonthly where company = '1234'
        select top 3  * from @mytable
return
GO
But when executing it with ADO in ASP page, it doesn't seem to return any data? Although I have no error with opening the recordset?

I use the following codes to do this:

Code:
        Set cmd = CreateObject("ADODB.Command")
        Set rs = CreateObject("ADODB.Recordset")
        cmd.ActiveConnection = adoCnn
        cmd.CommandText = "TEST"
        cmd.CommandType = adCmdStoredProc
        set rs = cmd.Execute

Is there any issue I need to take care of? It seems to have something to do with the table variable?
Reply With Quote