p2p.wrox.com Forums - Use Temp Table Variable in Stored Procedure

p2p.wrox.com Forums (http://p2p.wrox.com/)

- SQL Server ASP (http://p2p.wrox.com/sql-server-asp-101/)

- - Use Temp Table Variable in Stored Procedure (http://p2p.wrox.com/sql-server-asp/27477-use-temp-table-variable-stored-procedure.html)

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?

Answering my own question and for others who might benefit:

This is what I need to do, I added "SET NOCOUNT ON" in the beginning of the stored procedure to send back a closed recordset.

I wrote a post about this a while ago - you essentially need to form the sql statement first by declaring a long varchar and then executing it.

http://www.geakeit.co.uk/2011/02/05/a-table-input-variable-in-a-stored-procedure-and-bypassing-must-declare-the-table-variable-msg-1087/