Temp table cursor and dynamic sql build

[janetb99]

I need the ability to build an sql statement based on variables, use the local temp table option to keep users separated, and then cursor through the recordset to actually determine and calculate inserts into another temp table for selection. I've read that cursors can be slow, but because of my circumstance, would it not be better on the server (not a heavily hit sql server) than iterating through the records on the client-side where it's possible you're looking at a 28k modem and a pentium?

I'm having trouble finding a way to execute the dynamic sql from the cursor and/or referencing the temp table in the cursor after it's populated. It'll be obvious that SQL programming is not my forte so any help would be greatly appreciated.

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

SET NOCOUNT ON

------------Procedure to populate selected tables and display
declare @sql as varChar(1000), @varStat as varChar(50), @varStat2 as varChar(50), @my0 as decimal(28,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(28,3), @varCo as int


--build a global temp table for the second insert
SET @sql = 'CREATE TABLE ##' + CONVERT(varChar(15),@tblName) + ' (tblID int, [year] int, lineOrder int, titleID int, stat varChar(50), statTypeID int, countyID int, stat2 varChar(50))'
EXEC (@sql)

--build initial select
set @sql='INSERT INTO chad.dbo.ztblIncDispl SELECT s.stat, s.statTypeID, s.countyID, t.tblID, s.[year], t.calcYN, t.lineOrder, s.titleID, '+ CONVERT(varChar(15),@tblName) + ' as tblName 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)

--get dataset
set @varTbl=0
set @varYr=0
set @varCalc=0
set @varTot=0
set @varCo=0

-----Here's where I'd love to build the select statement
-----and execute directly without having a temp table
-----at all but I've tried various forms of executing the
-----cursor statement and can't get it to work.

DECLARE myCur SCROLL CURSOR FOR
SELECT stat, statTypeID, countyID, tblID, [year], calcYN, lineOrder, titleID FROM chad.dbo.ztblIncDispl WHERE tblName=@tblName ORDER BY [year], tblID, countyID, lineOrder
FOR READ ONLY
OPEN myCur
FETCH NEXT FROM myCur INTO @my0, @my1, @my2, @my3, @my4, @my5, @my6, @my7
WHILE @@FETCH_STATUS = 0
BEGIN
set @varStat=CONVERT(varChar(50),@my0)
set @varStat=CONVERT(varChar(30),@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!=1
set @varStat2=''
IF @varCalc=1
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(30),round(CONVERT(decimal(28,3),@varStat)/@varTot*100,2))
END
END
END


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

EXEC (@sql)
set @varTbl=@my3
set @varYr=@my4
set @varCo=@my2

IF @my6=1 --lineOrder=1
BEGIN
set @sql='INSERT INTO chad.dbo.tblDisplayCounties (tblName, tblID, countyID, [year], stat2) VALUES (''' + CONVERT(varChar(50),@tblName) + ''',' + CONVERT(varChar(4),@my3) +','+ CONVERT(varChar(3),@my2) +','+ CONVERT(varChar(4),@my4) +',''' + @varStat2 + ''')'
EXEC (@sql)
END

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



DELETE chad.dbo.ztblIncDispl WHERE tblName=@tblName

--get recordset for display
set @sql='SELECT d.tblID, d.[year], d.titleID, d.stat, d.statTypeID, d.stat2, s.lineTitle, t.tblTitle, u.source, u.sourceURL FROM ##' + CONVERT(varChar(15),@tblName)
set @sql=@sql + ' d 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)

 

[SQLSister]

Oh dear, I don't even know where to start. First forget you ever hear the term cursor. Really this is the best way. Learn to use set commands. In this case you need to learn to use the Case function and it should solve your problems. Also you shouldn't need a global temp table which is accessible outside your stored procedure. A local one will do.

OK you seem to be under a misconception about the cursors. Iterating through records is slow becasue it has to look at every record and see if it meets the conditions. Set statements are fast becasue they process all at once. The user connection is irrelevant as they don't get the information until you are finished anyway. So if the set statement would return 20 results to the user so would the cursor doing the same thing, it just would take up much more of your server's processing time.

I don't really feel like wading through what you posted and figuring out what you really intended to do, so please, write in English, not code exactly what you want the stored procedure to do to the data before you insert it. Maybe then we can help you with specifics.

 

[bassguy]

I would agree with SQL sister in that I have learned after creating many a query like your example that there is often a way to get better results using derived tables and case statements. it will be worth the investment of your time to brush up on those..


bassguy