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?
Use Temp Table Variable in Stored Procedure
