June 3, 2002 at 1:43 am
I've been trying to use temporary tables in sp's over time and have had no luck with them. I usually create and delete actual tables which of course can create conflicts if the sp is run by more than one person. (I could create unique names but this is very messy.)
My problem is that I can get the T-sql to run fine and return records in query analyser but if I try to run it via ADO or an .adp then I get no records. I assume it is due to query analyser still keeping the session open whereas elsewhere, when the records are returned, the table no longer exists. Is there a way around this?
I'm using ver7.
Cheers,
Mike,
The proud father of my very first post.
June 3, 2002 at 2:39 am
pls trying to use table varibal insted of TMP table
by
DECLARE @TableName TABLE
(
Fields
)
Shrinivas L.K.
Shrinivas L.K.
June 3, 2002 at 2:52 am
I tried them in the past but I'm using using SQL7. (I use SQL2000 query analyser so new of their existance - frustratingly)
Cheers,
Mike
June 3, 2002 at 4:34 am
This is the basic strucutre of how I use a temp table in SP and has not failed me.
CREATE PROC ip_ProcName
--Variables Here
AS
SET NOCOUNT ON
CREATE #TEMPTABLEHERE (
[COLUMNSOFTEMPTABLE] [datatype] (NOT) NULL
)
INSERT INTO #TEMPTABLEHERE (COLUMNSOFTEMPTABLE) SELECT MATCHINGCOLUMNS FROM QUERYCONSTRAINTS.....
SELECT COLUMNSOFTEMPTABLE FROM #TEMPTABLEHERE
DROP TABLE #TEMPTABLEHERE
GO
If you have a piece of code that fails if will post we can take a look and see if anything specific stands out.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 3, 2002 at 5:08 am
pls trying to use exists object
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].tmptablename') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tmptablename]
GO
Shrinivas L.K.
Shrinivas L.K.
June 3, 2002 at 6:51 pm
Excellent, thanks Antares686. After eliminating sections I found that what I was doing is leaving the <SET NOCOUNT ON> out of the stored proc. One of those things that I've wondered why it's so prominently used in examples and every properly trained SQL programmer probably knows why.
I imagine that when the proc has run it goes to do a count and can't find the table (even if you leave out the drop table 'suchandsuch').
And Shrinivas, the EXISTS select statement won't work as it is.
After a bit of investigation...
The temporary table exists in sysobjects in the tempdb database not master. The name created is the name given in the sp plus a unique identifier suffixed onto it. If you want to identify it in object_id() you need to specify the tempdb database and it will find the correct one for that session. This is how it should read...
select *
from tempdb.dbo.sysobjects
where id = object_id(N'tempdb..#temp')
A couple of other things...
I left off the OBJECTPROPERTY statement as I can't see why you would need that - is it just a failsafe in case you specify the wrong name. And this unicode thing. I often leave it off - what's the danger here?
cheers,
Mike
June 3, 2002 at 7:17 pm
SET NOCOUNT ON turns off the record affected messages which can cause issues with your recordset if you have an insert and a select. Also elminating these messages reduces overhead on the network especially when you have a high transaction site. As for the exists offered, I personally never use for sp's since I drop at the end and the temp table should not exist. However, it probably is better to put the failsafe in than not. Now as for N unicode thing, I only use when unicode data is required such as expected by and nchr, nvarchar, etc field. Otherwise I generally don't bother with, but this is me and I am familiar with my system.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply