Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cursors Expand / Collapse
Author
Message
Posted Wednesday, June 17, 2009 6:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 03, 2014 12:37 AM
Points: 10, Visits: 21
--Can AnyOne Help me in this ...
I am using the following Procedure to open a Cursor to update some data from a table to another Temporary Table, but I am not getting the desired results, because the Cursor update the table with the following data in all the fields?

Code = 509
GPOName = ABC
TotalMO = 25259

CREATE PROCEDURE DailyUPD
AS

Declare @Code as int
Declare @GPOName as varchar(50)
Declare @TotalMO as varchar(50)

Begin

DECLARE DailyUPD_Cursor CURSOR FOR

select distinct(masterdata.gpo_id) as Code, gpo.name as "GPO Name", count(masterdata.mno) as "Total MO" from masterdata, gpo
where masterdata.gpo_id = gpo.gpo_id
group by masterdata.gpo_id, gpo.name
order by masterdata.gpo_id

OPEN DailyUPD_Cursor

FETCH NEXT FROM DailyUPD_Cursor into @Code, @GPOName, @TotalMO

WHILE @@FETCH_STATUS = 0

BEGIN
Update tempDailyUPD
Set Code = @Code, GPOName = @GPOName, TotalMO = @TotalMO
FETCH NEXT FROM DailyUPD_Cursor into @Code, @GPOName, @TotalMO
-- FETCH NEXT FROM DailyUPD_Cursor
END

CLOSE DailyUPD_Cursor
DEALLOCATE DailyUPD_Cursor

End
GO
Post #736465
Posted Wednesday, June 17, 2009 6:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:26 AM
Points: 1,949, Visits: 8,291
There are no sarg's on you update statement,

You probably meant to say

Update Table
Where current of CursorName

Take a look in books online for more details.

I would also argue that you dont need a cursor for this operation at all.

Try to get all your logic into one update statement , your server will be much happier for the effort




Clear Sky SQL
My Blog
Kent user group
Post #736470
Posted Wednesday, June 17, 2009 6:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932
There is no reason for the cursor. Try a joined UPDATE to get less code and much better performance:
UPDATE t SET
t.Code = md.Code,
t.GPOName = md.GPONam,
t.TotalMO = md.TotalMO
FROM tempDailyUPD
JOIN
(
select
distinct(masterdata.gpo_id) as Code,
gpo.name as GPOName,
count(masterdata.mno) as TotalMO
from masterdata, gpo
where masterdata.gpo_id = gpo.gpo_id
group by masterdata.gpo_id, gpo.name
) md
ON t.[Your Destination JOIN Column] = md..[Your Source JOIN Column]

Notice the ON part of the JOIN. Here you have to define the relation between both tables which seems to be missing in your script.

Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #736480
Posted Thursday, June 18, 2009 12:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 03, 2014 12:37 AM
Points: 10, Visits: 21
Thanx |Ten Centuries| This is much helpful
But I am facing another thing which is a bit complex, I have to run the following procedures in the same I have mentioned above by passing the same variables in the clause as; (@YearMonth, @sDate, @eDate), following are the queries I am trying to pass in these SP:

Begin
Execute CountStatusD @YearMonth
Execute CountStatusU @YearMonth
Execute CountStatusNull @YearMonth
Execute DisposedBefore @sDate, @eDate, @YearMonth
Execute DisposedToday @sDate, @eDate, @YearMonth
End

SELECT DISTINCT masterdata.gpo_id AS Code, gpo.name AS [GPO Name], COUNT(masterdata.mno) AS [Total MO]
FROM masterdata INNER JOIN
gpo ON masterdata.gpo_id = gpo.gpo_id
WHERE (masterdata.year_month = @YearMonth)
GROUP BY masterdata.gpo_id, gpo.name
ORDER BY masterdata.gpo_id

SELECT DISTINCT gpo_id AS Code, COUNT(mno) AS Delivered
FROM masterdata
WHERE (year_month = @YearMonth) AND (status = 'D') -- Here I will pass Three Status as "U and NULL"
GROUP BY gpo_id
ORDER BY gpo_id

SELECT DISTINCT (masterdata.gpo_id) AS Code, COUNT(mno) AS DisposedBefore -- The Same will run as DisposedToday
FROM masterdata
WHERE status IS NOT NULL AND year_month = @YearMonth AND date_reported >= @sDate AND date_reported <= @eDate
GROUP BY masterdata.gpo_id
ORDER BY masterdata.gpo_id

Would there be a scenario to run all these queries as one, because I have to update the same tempDailyUPD Table by running all these SP/Queries, like;

Update tempDailyUPD
Set DisposedBefore = @DisposedBefore where Code=@Code

Please help me ...
Post #737176
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse