Recordset empty when using temporary table in a Stored Procedure

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

SQL Server Development (2000)

Recordset empty when using temporary table in a Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-26 : 09:51:48

Patrick Vliegen writes "Hello,
I have found something very strange about Procedures and Temporary tables.
Let's see the very simple following procedure :

**Procedure Version 1**
CREATE PROCEDURE MyProcedure
AS
SELECT column1, column2 FROM dbtable
GO
**Procedure Version 1**

If you call that procedure using ADO within an ASP page for instance, you will get a recorset containing records :

Set Conn = Server.CreateObject("ADODB.Connection")
Set RSt = Server.CreateObject("ADODB.RecordSet")
Conn.Open strConnexion

strProcExec = "exec MyProcedure"

RSt.Open strProcExec, Conn, adOpenKeyset, adLockReadOnly
If RSt.RecordCount > 0 Then
Response.Write "Total Records: " & RSt.RecordCount
Else
Response.Write "No Records"
End If

RSt.Close
Set RSt = Nothing

So here the "RecordCount" property of the Recordset will contain a value <> -1.
Note that the CursorType adOpenKeyset is important here to get something for the RecordCount property.

Let's now change a bit our Stored Procedure by declaring a Temporary table.

**Procedure Version 2**
CREATE PROCEDURE MyProcedure
AS

Create Table #myTempTable
(
TempColumn1 int NOT NULL,
TempColumn2 int NOT NULL
)

SELECT column1, column2 FROM dbtable
GO
**Procedure Version 2**

As you can see here I do not do anything with the temp table but whatever you use that temporary table or not, the simple fact it is there, if you call the procedure again you will see the Recordset is EMPTY !!!!!
No record is returned by the procedure anymore, the RecordCount property equals -1, but it is not true there is something but it seems ADO does not see it.

What is weird is that if you execute that procedure, with or without the Temporary Table declaration but using the SQL Query Analyser, then you always see the records returned by the procedure.

I am very embarrassed cause I need the result of a Procedure using Temporary Table and I can not get it working.
I can not find on your site or anywhere else an explanation on this strange phenomene.

Could you please have a look at my request and help me on solving it.
Thank you.
Patrick.

Here is my configuration :
Windows 2000 Server SP2
SQL Server 2000 SP2
Enterprise Manager 1.2
SQL Query Analyser 8.00.194
ADO 2.6"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-26 : 10:00:19

quote:

As you can see here I do not do anything with the temp table but whatever you use that temporary table or not, the simple fact it is there, if you call the procedure again you will see the Recordset is EMPTY !!!!!
No record is returned by the procedure anymore, the RecordCount property equals -1, but it is not true there is something but it seems ADO does not see it.

There is a distinction between an empty recordset and a recordset having a RecordCount property < 0. Is the (recordset).EOF() true after calling the second version of the procedure?

I would recommend you do one of the following:
1) Alter the method of opening the recordset, from adOpenKeyset to adOpenStatic. It's been a while since I've used ADO but if memory serves you've got a better chance of populating the RecordCount property with that option.

2) Have the procedure return @@rowcount and use an ADO command object to call the procedure and get the returned value, thus avoiding a dependency on RecordCount altogether.

Jonathan Boott, MCDBA

andre
Constraint Violating Yak Guru

259 Posts

Posted - 2002-06-26 : 10:49:54

At the beginning of your stored procedure, add this line:

SET NOCOUNT ON

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources