How to return a result set from temporary table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to return a result set from temporary table

Author

Topic

reemjacob
Starting Member

6 Posts

Posted - 2003-04-01 : 15:41:55

hello everybody,

I am trying to create a temporary table in my stored procedure and use the result set in the calling program( which is in VB). I am using ADO. I am getting an error when I run the VB. operation is no allowed when the object is closed. I have marked the line where it occurs. What am I doing wrong?

This is my stored proc.

CREATE PROCEDURE sp_get_total_qty_rate
@customerid char(15),
@datefrom datetime,
@dateto datetime

AS

DECLARE @dDateFrom datetime, @dDateTo datetime, @sCustomerid char(15), @fdnqty numeric, @nRate numeric

/* Create temporary table*/
CREATE TABLE #TempTable ( Quantity numeric, Rate numeric)

INSERT INTO #TempTable VALUES ( 123, 20 )

SELECT * FROM #TempTable

RETURN
GO

Below is the code in VB where I am calling the stored proc.
Dim oRSRate As New ADODB.Recordset
Dim oCmd As New ADODB.Command
Dim oParam As New ADODB.Parameter
Dim oConn As New ADODB.Connection
Dim sStr As String

oConn.Open sConn
Set oCmd.ActiveConnection = oConn
oCmd.CommandText = "sp_get_total_qty_rate"
oCmd.CommandType = adCmdStoredProc
Set oParam = oCmd.CreateParameter("customerid", adChar, adParamInput, 15, CustomerID.Value)
oCmd.Parameters.Append oParam
Set oParam = oCmd.CreateParameter("datefrom", adDate, adParamInput, , Format(amDateFrom.Value, "yyyy/mm/dd"))
oCmd.Parameters.Append oParam
Set oParam = oCmd.CreateParameter("dateto", adDate, adParamInput, , Format(amDateTo.Value, "yyyy/mm/dd"))
oCmd.Parameters.Append oParam
oCmd.CommandTimeout = 30
Set oRSRate = oCmd.Execute()

/*** This is the statement wher the error occurs **********/
While Not oRSRate.EOF
sStr = sStr & oRSRate("Quantity") & " " & oRSRate("Rate") & vbCrLf
oRSRate.MoveNext

Wend

MsgBox sStr
oRSRate.Close

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-01 : 16:11:58

After the stored procedure is execute, the temporary table is dropped. This is how temporary tables work. They exist only for the session, and in this case, your session is the stored procedure. So you will need to do all work with the temporary table inside the stored procedure.

Tara

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-04-01 : 19:58:28

Actually, you simply need to add:

CREATE PROCEDURE sp_get_total_qty_rate
@customerid char(15),
@datefrom datetime,
@dateto datetime
AS
SET NOCOUNT ON
DECLARE @dDateFrom datetime, @dDateTo datetime, @sCustomerid char(15), @fdnqty numeric, @nRate numeric

/* Create temporary table*/
CREATE TABLE #TempTable ( Quantity numeric, Rate numeric)

INSERT INTO #TempTable VALUES ( 123, 20 )

SELECT * FROM #TempTable

RETURN

The INSERT statement will generate a "rows affected" message that confuses ADO and prevents the temp table from being SELECTed properly. Usually whenever you have problems with temp tables and ADO recordsets it's because of SET NOCOUNT. It's a good habit to include SET NOCOUNT ON as the first line of every stored procedure you write.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources