Temp tables and fetch cursors - SQL Server Forums

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)

Temp tables and fetch cursors

Author

Topic

janetb
Yak Posting Veteran

71 Posts

Posted - 2003-04-29 : 09:28:07

I'm trying to execute the following and kinda get the batch problem with local temp tables. I'd really like to use the #temp for the isolation provided, but continue to get invalid object. Looked at http://www.sqlteam.com/item.asp?ItemID=4619, but the problem may be in the later DECLARE myCur CURSOR LOCAL FOR call. I'd create my own table and then drop it, but I can't figure out how to create the sql to execute the cursor statement with @sql='blah blah' + @tblName + 'blah'

Any help?

--build initial select into a temp table, then another for cursor table
SET @sql = 'CREATE TABLE #temp (stat decimal(25,3), statTypeID int, countyID int, tblID int, [year] int, calcYN int, lineOrder int, titleID int)'
EXEC (@sql)

--build initial select
select @sql='INSERT #temp SELECT s.stat, s.statTypeID, s.countyID, t.tblID, s.[year], t.calcYN, t.lineOrder, s.titleID FROM chad.dbo.tblStats s'
set @sql=@sql + ' RIGHT OUTER JOIN chad.dbo.tblStatTitles t ON s.titleID = t.titleID WHERE (s.levID=2) AND '
set @sql=@sql + ' (s.countyID IN ' + @coID + ') AND (t.tblID IN ' + @tblID + ') '
if @year != 0
set @sql=@sql + ' AND (s.[year]=' + CONVERT(varChar(4),@year) + ') '
EXEC (@sql)

SET @sql = 'CREATE TABLE ' + @tblName + ' (tblID int, [year] int, lineOrder int, titleID int, stat decimal(25,3), statTypeID int, countyID int, stat2 varChar(25))'
EXEC (@sql)

--get dataset
set @varTbl=0
set @varYr=0
set @varCalc=0
set @varTot=0
set @varCo=0
set @varStat=0
DECLARE myCur CURSOR LOCAL FOR
SELECT stat, statTypeID, countyID, tblID, [year], calcYN, lineOrder, titleID FROM #temp ORDER BY [year], tblID, countyID, lineOrder
OPEN myCur

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-29 : 09:40:36

have you tried combinining the two @sql strings and using a GO statement in between but that still won't help you with the cursor.

It would work with a ## but that is very bad in a multiuser environment.

What about posting what you're trying to accomplish and we'll see if we can remove the need for a cursor.

Why are you creating temp dynamically if it's because of @year you can just add that criteria to the where clause like

s.[year] = blahahahah And @year <> 0
or use a case statement if the convert gives you problems.

janetb
Yak Posting Veteran

71 Posts

Posted - 2003-04-29 : 10:32:34

Okay, here's the whole thing. I had originally put it into an ADO recordset array and then called separate procedures, but it was pretty slow at times. So, after researching on the web a bit, I came across speeding up iterations using the fetch cursor. Programming isn't my first calling, so bang away and let me have it.

I have to pull records based on one set of criteria (year, coID, tblID) and then calculate another set of records and display them, but only based on the first item of (tblID) set to calcYN=1 and statTypeID=1.

CREATE PROCEDURE pTblTempDisplayInsert2 @new as int, @year as int, @coID as varChar(100), @tblID as varChar(500), @tblName as varChar(25) AS

SET NOCOUNT ON

declare @sql as varChar(1000), @varStat as decimal(13,3), @varStat2 as varChar(30), @my0 as decimal(13,3), @my1 as int, @my2 as int, @my3 as int, @my4 as int, @my5 as int, @my6 as int, @my7 as int, @varTbl as int, @varYr as int, @varCalc as int, @varTot as decimal(13,3), @varCo as int

IF @new<3
--build initial select into a temp table, then another for cursor table
SET @sql = 'CREATE TABLE ##temp (stat decimal(25,3), statTypeID int, countyID int, tblID int, [year] int, calcYN int, lineOrder int, titleID int)'
EXEC (@sql)

--build initial select
select @sql='INSERT ##temp SELECT s.stat, s.statTypeID, s.countyID, t.tblID, s.[year], t.calcYN, t.lineOrder, s.titleID FROM chad.dbo.tblStats s'
set @sql=@sql + ' RIGHT OUTER JOIN chad.dbo.tblStatTitles t ON s.titleID = t.titleID WHERE (s.levID=2) AND '
set @sql=@sql + ' (s.countyID IN ' + CONVERT(varChar(3),@coID) + ') AND (t.tblID IN ' + CONVERT(varChar(5),@tblID) + ') '
if @year != 0
set @sql=@sql + ' AND (s.[year]=' + CONVERT(varChar(4),@year) + ') '
EXEC (@sql)

SET @sql = 'CREATE TABLE ' + @tblName + ' (tblID int, [year] int, lineOrder int, titleID int, stat decimal(25,3), statTypeID int, countyID int, stat2 varChar(25))'
EXEC (@sql)

--get dataset
set @varTbl=0
set @varYr=0
set @varCalc=0
set @varTot=0
set @varCo=0
set @varStat=0
DECLARE myCur CURSOR LOCAL FOR
SELECT stat, statTypeID, countyID, tblID, [year], calcYN, lineOrder, titleID FROM ##temp ORDER BY [year], tblID, countyID, lineOrder

OPEN myCur
FETCH NEXT FROM myCur INTO @my0, @my1, @my2, @my3, @my4, @my5, @my6, @my7
WHILE @@FETCH_STATUS = 0
BEGIN
if (@varYr <> @my4) or (@varTbl <> @my3) or (@varCo<>@my2)
BEGIN
set @varTbl=0
set @varYr=0
set @varCalc=0
set @varTot=0
set @varCo=0
set @varStat=0
END

set @varStat=@my0
IF @my6=1 --lineOrder=1
BEGIN
IF @my5=1 --Calc Y or N
BEGIN
set @varCalc=1
set @varTot=@my0
END
END
IF @varCalc=0
set @varStat2=''
IF @varCalc!=0
BEGIN
IF @my6=1 --lineOrder=1
set @varStat2='100%'
IF @my6!=1 --lineOrder<>1
BEGIN
IF @my1!=1
set @varStat2='NA'
IF @my1=1
BEGIN
IF @varTot=0
set @varStat2='0'
IF @varTot!=0
set @varStat2=CONVERT(varChar(20),CONVERT(decimal(25,2),round(@varStat/@varTot*100,2))) + '%'
END
END
END

set @sql='INSERT INTO ' + @tblName + ' (tblID, [year], lineOrder, titleID, stat, statTypeID, countyID, stat2) VALUES ('
set @sql=@sql + CONVERT(varChar(4),@my3) + ', '+ CONVERT(varChar(4),@my4) +', '+ CONVERT(varChar(2),@my6) +', '
set @sql=@sql + CONVERT(varChar(5),@my7) +', '+ CONVERT(varChar(28),@varStat) +', '+ CONVERT(varChar(1),@my1) +', '+ CONVERT(varChar(3),@my2) +', '''+ @varStat2 +''')'
EXEC (@sql)
set @varTbl=@my3
set @varYr=@my4
set @varCo=@my2

FETCH NEXT FROM myCur INTO @my0, @my1, @my2, @my3, @my4, @my5, @my6, @my7
END
CLOSE myCur
DEALLOCATE myCur

set @sql='SELECT d.tblID, d.[year], d.titleID, d.stat, d.statTypeID, d.stat2, s.lineTitle, t.tblTitle, u.source, u.sourceURL FROM ' + @tblName + ' d '
set @sql=@sql + 'LEFT JOIN chad.dbo.xtblTitles t ON d.tblID=t.tblID LEFT JOIN chad.dbo.tblStatTitles s ON d.titleID=s.titleID LEFT JOIN chad.dbo.xtblURL u ON u.urlID=t.urlID '
set @sql=@sql + 'ORDER BY d.tblID, d.[year], d.lineOrder, d.countyID'
EXEC (@sql)

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-29 : 10:43:09

how about some sample data, sample results and a small description of what you want to speed things up.

PS:
Indentation is important when others are trying to read your code.

janetb
Yak Posting Veteran

71 Posts

Posted - 2003-04-29 : 11:43:18

Many apologies - I copy/pasted the item from the stored procedure and it stripped my indents. (How do you indent in the forum code?) Please advise if you'd like me to repost for easy reading.

The reason I'd like to speed in up is location and possible items. Many connections here are still limited to 28k modems. Some of the tables can have 80 line items and there are potentially 2000 tables and up to 17 counties. Most will probably not select all, but I'd like to make the pull as fast as possible. I thought that doing most of the work on the server side using fetch cursor would make the data selection/population/selectDisplay as fast as possible under the circumstances.

initial pull for countyID=113, tblID=111 or tblID=104 would be:

stat.StatTypeID..countyID..tblID....year..calYN..lineOrder..titleID
22.....2.................113.......111.....2000....0.......1.........1613
64.....2.................113.......111.....2000....0.......3.........1615
59.....1.................113.......111.....2000....0.......2.........2110
170....1.................113.......111.....2000....0.......4.........2112
22.....1.................113.......104.....1999....0.......4.........1742
4.......1.................113.......104.....1999....0.......2.........2118
201....1.................113.......104.....1999....0.......3.........2119
331....1.................113.......104.....1999....1.......1.........2135

Notice one table (104 would have a calculated stat2 (then converted to varChar with %))
but the other would not.

I need to keep the new set in a table so that I can pull just the lineOrder=1, countyID, stat2 to read so that I know when I write out the html code how to display the column titles - since one table will have an extra column and one won't.

Display would resemble:

table 104: Motor Vehicle Crashes, (Year 1999)
............................................................Percent
..................................................Macon....of.Total
.Total.Vehicle.Crashes.........................331.......100%
.Total.Traffic.Injuries.Fatal......................4......1.21%
.Total.Traffic.Injuries.Nonfatal...............201.....60.73%
.Total.Alcohol.or.Drug.Related.Crashes.....22......6.65%

table 111:Smoking in Middle School, (Year 2000)
.........................................................Macon
.Percent.of.Students.Smoking.....................22%
.Total.Students.Smoking............................59
.Percent.with.Smokers.in.Household...............64%
.Total.Students.with.Smokers.in.Household.....170

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources