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:


    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

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:


        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

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.


All times are GMT -4. The time now is 03:48 PM.

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