p2p.wrox.com Forums

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)

rweide March 30th, 2005 11:07 AM

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?

rweide March 30th, 2005 03:35 PM

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.


joshgeake April 15th, 2011 03:50 PM

solution
 
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/


All times are GMT -4. The time now is 08:05 AM.

Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
2013 John Wiley & Sons, Inc.