I'm playing with simple stored procedure with declared table variable as below:
CREATE PROCEDURE TEST
Declare @myTable table
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?