Trying to replace the cursor - 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

General SQL Server Forums

New to SQL Server Programming

Trying to replace the cursor

Author

Topic

sravz
Starting Member

42 Posts

Posted - 2011-06-13 : 14:44:45

Hi Every one,

I AM USING SQL SEVRER 2008.I am stuck up with this cursor.......
THIS IS THE REQUIREMENT I GOT FROM MY MANAGER.
Here is my problem I have view called vwLMCLaborHoursCompare3 which has Two rows with names suspensefile and labor hours with values as follows.
suspensefile(varchar) laborhours(decimal)
1234 0.00
2323 1.00
1121 2.00

So NOW BASED ON THESE VALUES IN NEED TO UPDATE THE POHISTORY

LIKE THIS
SUSPENSE FILE LABORHOURS POHOURS(DECIMAL)
{(POHistory) (VIEW) (POHISTORY)
(view)} ----------------------------(RELATED TABLES AND VIEWS)
1234-------------0.00-------------------1.00
2323-------------1.00(NOT EQUAL TO 0.00)1.00(SAME AS LABORHOURS)
1121-------------2.00(NOT EQUAL TO 0.00)2.00(SAME AS LABORHOURS)

For looping through each row for suspense file I used following stored procedure,because cursor is taking really too long time.

-----THIS IS THE STORED PROCEDURE

ALTER PROCEDURE UpdatePOPartDetailHistory
AS
SET NOCOUNT ON

-- declare all variables!

DECLARE @iReturnCode varchar(39),

@iNextRowId varchar(39),

@iCurrentRowId varchar(39),

@vchSuspense_File varchar(39),

@iLoopControl int,

@vchTCLaborHours DECIMAL

SELECT @iLoopControl = 1

SELECT @iNextRowId = MIN(Suspense_File)

FROM #tmpSingleSF

IF ISNULL(@iNextRowId,0) = 0

BEGIN

SELECT 'No data found in table!'

RETURN

END

SELECT @iCurrentRowId = Suspense_File,

@vchTCLaborHours = TCLaborHours

FROM vwLMCLaborHoursCompare3

WHERE Suspense_File= @iNextRowId

-- start the main processing loop.

While( @iLoopControl = 1)

BEGIN

IF(@vchTCLaborHours='0.00')

BEGIN

UPDATE tblPOPartDetailHistory

SET POInspectionHours= 1.00

WHERE Suspense_File=@iCurrentRowId

END

ELSE

BEGIN

UPDATE tblPOPartDetailHistory

SET POInspectionHours= @vchTCLaborHours

WHERE Suspense_File=@iCurrentRowId

END

SELECT @iNextRowId = NULL

SELECT @iNextRowId = MIN(Suspense_File)

FROM #tmpSingleSF

WHERE Suspense_File> @iCurrentRowId

IF ISNULL(@iNextRowId,0) = 0

BEGIN

BREAK

END

SELECT @iCurrentRowId = Suspense_File,

@vchTCLaborHours = TCLaborHours

FROM vwLMCLaborHoursCompare3

WHERE Suspense_File= @iNextRowId

END

RETURN

------------THIS TEMP TABLE IS CREATED TO TAKE ONLY SINGLE SUSPENSEFILE RECORDS
SELECT * INTO #tmpSingleSF FROM

(SELECT suspense_File, COUNT(suspense_file) AS CntSF, SUM(TCLaborHours) AS LaborHours from vwLMCLaborHoursCompare3

GROUP BY Suspense_File) T1

WHERE CntSF = 1

EXEC UpdatePOPartDetailHistory

I CANNOT CHANGE THE EXISTING DATABASE BECAUSE IT IS BEING USED BY LOT MANY CLIENT,AND I AM NOT SUPPOSED TO CHANGE .
SO THE ONLY WAY IS TO WRITE THE CURSOR OR USE THIS ALTERNATE STORED PROCEDURE.......BUT THE CURSOR DOESNOT WORK BECAUSE IT IS TAKING TOO MUCH TIME.
BUT THIS PROCESS IS NOT WORKING ......SO ANY SUGGESTIONS PLEASE

HERE IS THE LINK I USED TO WRITE THIS ALTERNATIVE STORED PROCEDURE IN PLACE OF CURSOR.

http://www.sql-server-performance.com/2004/operations-no-cursors/2/

THANKS,
SRAVZ

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-06-13 : 14:50:13

quote:

BUT THIS PROCESS IS NOT WORKING

Not enough information.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-13 : 16:32:39

People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

It looks like you are using SQL to write code in 1960's BASIC -- prefixes, row numbers, and LOOPS in SQL? ARRRGHH!!

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-13 : 17:42:08

I believe this will do the trick:

ALTER PROCEDURE UpdatePOPartDetailHistory AS
SET NOCOUNT ON

SELECT suspense_File INTO #tmpSingleSF 
FROM vwLMCLaborHoursCompare3 GROUP BY Suspense_File
HAVING COUNT(suspense_file)=1

UPDATE H SET POInspectionHours=CASE WHEN V.TCLaborHours=0 THEN 1 ELSE V.TCLaborHours END
FROM tblPOPartDetailHistory H
INNER JOIN #tmpSingleSF S ON H.Suspense_File=S.Suspense_File
INNER JOIN vwLMCLaborHoursCompare3 V ON H.Suspense_File=V.Suspense_File

RETURN

sravz
Starting Member

42 Posts

Posted - 2011-06-14 : 09:40:52

Hey robvolk,

That worked great....Thanks for the help.I was strugling with this for past 1 month.I could have done this earlier.

I never saw an UPDATE with JOIN STATEMENTS and more over with a 'FROM'.I am excited to learn this.Could you please explain me how the UPDATE STATEMENT WORKS.

Thanks very very much.

Sravz

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-14 : 09:47:01

More details here: http://msdn.microsoft.com/en-us/library/ms177523.aspx

In Transact-SQL you can specify a FROM clause that allows you to JOIN tables for an UPDATE. You'd reference the table you want to UPDATE by its alias; in the example I posted, that would be "H", referring to tblPOPartDetailHistory. (you can use any alias, I just like short names) The rest of the UPDATE is pretty standard SQL.

sravz
Starting Member

42 Posts

Posted - 2011-06-14 : 10:24:09

Thanks very much for the help.

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-06-14 : 10:36:48

Even before scrolling down I knew Celko would have replied to this, so funny! I hate it when people prefix variable names with something describing the data type almost as much as when people write in caps... ugly. Who needs set based queries when you can do everything with a procedural type loop?

Nice so see sravz is learning the set based method though, that's where to start. Unlearn what you have learnt (i.e. forget cursors exist and forget procedural methods!)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources