can we avoid cursors in the stored procedure?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

can we avoid cursors in the stored procedure??

Author

Topic

pt_at_us
Starting Member

2 Posts

Posted - 2005-05-23 : 17:18:46

Hi,
I need urgent help. i have written this sp using cursors, but i doubt if it is good for database performance. can any one please give a solution to this sp without using cursor. your help is greatly appreciated.

CREATE PROCEDURE salesDel_Duplicates AS

DECLARE @salunquid varchar(11),@salunquid1 varchar(11)
DECLARE @perec_ind varchar(10),@perec_ind1 varchar(10), @pe_sid varchar(10), @pe_sid1 varchar(10)
DECLARE @prrec_ind varchar(10),@prrec_ind1 varchar(10), @pr_sid varchar(10), @pr_sid1 varchar(10)

/* gathering salefacttable data in cursor C1 for duplicate records comparision*/
DECLARE C1 CURSOR FOR
SELECT salesuniqueid,peoplesid,productsid,businesssid,customerInvoiceAddressSid,customerDeliveryAddressSid,customerSid
FROM salesfact

/* gathering salefacttable data in cursor C2 for duplicate records comparision */
DECLARE C2 CURSOR FOR
SELECT salesuniqueid,peoplesid,productsid,businesssid,customerInvoiceAddressSid,customerDeliveryAddressSid,customerSid
FROM salesfact

OPEN C1
OPEN C2

--Fetch record from salesfact.
FETCH NEXT FROM C1
INTO @salunquid,@pe_sid,@pr_sid

WHILE @@FETCH_STATUS = 0

-- While for C1 begins here.
BEGIN

FETCH NEXT FROM C2
INTO @salunquid,@pe_sid1,@pr_sid1,@bu_sid1,@cuinad_sid1,@cudead_sid1,@cus_sid1

--Loop to Compare duplicate records in C1, C2 to determine the differentiating column in the duplicate record.

WHILE @@FETCH_STATUS = 0
-- While for C2 begins here.
BEGIN

--Comparison of duplicate records starts here.
if @salunquid = @salunquid1
begin

--Compares the sid fields of duplicate records.
if @pe_sid <> @pe_sid1
begin
--Gets currec_ind flag from the differentiator column.
select @perec_ind = currec_ind from people where peoplesid = @pe_sid
select @perec_ind1 = currec_ind from people where peoplesid = @pe_sid1

--Check for currec_ind column flag.
if @perec_ind='N'
begin
Begin transaction
--deletes the duplicate record from salesfact table.
delete from salesfact where SALESUNIQUEID = @salunquid and peoplesid=@pe_sid;
commit transaction
end

if @perec_ind1='N'
begin
Begin transaction
delete from salesfact where SALESUNIQUEID = @salunquid1 and peoplesid=@pe_sid1;
commit transaction
end
end
Else

if @pr_sid <> @pr_sid1
begin

select @prrec_ind = currec_ind from product where productsid = @pr_sid
select @prrec_ind1 = currec_ind from product where productsid = @pr_sid1

if @prrec_ind='N'
begin
Begin transaction
delete from salesfact where SALESUNIQUEID = @salunquid and productsid=@pr_sid;
commit transaction
end

if @prrec_ind1='N'
begin
Begin transaction
delete from salesfact where SALESUNIQUEID = @salunquid1 and productsid=@pr_sid1;
commit transaction
end
end

end

FETCH NEXT FROM C2
INTO @salunquid,@pe_sid1,@pr_sid1

--While for C2 ends here.
END

FETCH NEXT FROM C1
INTO @salunquid,@pe_sid,@pr_sid

--While for C1 ends here.
END

CLOSE C1
DEALLOCATE C1

CLOSE C2
DEALLOCATE C2
GO

thanks,
pt.

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-23 : 21:01:22

hihi... that's is why i hate cursors... anyway getting duplicate ids in a table is very simple
try this

Getting Duplicate IDs:

SELECT COUNT(salesuniqueid) AS salesuniqueid
FROM dbo.salesfact
GROUP BY salesuniqueid
HAVING (COUNT(salesuniqueid) > 1)

Deleting Duplicate ID's
DELETE FROM dbo.salesfact where salesuniqueid IN
(
SELECT COUNT(salesuniqueid) AS salesuniqueid
FROM dbo.salesfact
GROUP BY salesuniqueid
HAVING (COUNT(salesuniqueid) > 1)
)

simple... right...

"Wisdom is knowing what to do next, skill is knowing how to do it, and virtue is doing it. "

"The questions you ask consistently will create either enervation or enjoyment, indignation or inspiration, misery or magic. Ask the questions that will uplift your spirit and push you along the path of human excellence. "

K.I.S.S - Keep it simple stupid

raclede™

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-23 : 21:04:16

that is just an example use you creativity to derived that results you want from that example..
just manipulate it when you want to add more records...

"Wisdom is knowing what to do next, skill is knowing how to do it, and virtue is doing it. "

"The questions you ask consistently will create either enervation or enjoyment, indignation or inspiration, misery or magic. Ask the questions that will uplift your spirit and push you along the path of human excellence. "

K.I.S.S - Keep it simple stupid

raclede™

dursaliye
Starting Member

22 Posts

Posted - 2005-05-23 : 23:03:02

Try this one.

DELETE FROM dbo.salesfact WHERE salesuniqueid IN
(
SELECT salesuniqueid FROM dbo.salesfact t1
WHERE 1<(SELECT COUNT(salesuniqueid) AS salesuniqueid FROM dbo.salesfact t2 WHERE t2.salesuniqueid = t1.salesuniqueid)
)

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-23 : 23:12:14

quote:
Originally posted by dursaliye

Try this one.

DELETE FROM dbo.salesfact WHERE salesuniqueid IN
(
SELECT salesuniqueid FROM dbo.salesfact t1
WHERE 1<(SELECT COUNT(salesuniqueid) AS salesuniqueid FROM dbo.salesfact t2 WHERE t2.salesuniqueid = t1.salesuniqueid)
)

SELECT salesuniqueid FROM dbo.salesfact t1
WHERE 1<(SELECT COUNT(salesuniqueid) AS salesuniqueid FROM dbo.salesfact t2 WHERE t2.salesuniqueid = t1.salesuniqueid)
-- this query returns all duplicate salesID, ex. if you have Sales ID's.. 5,6,5,7,5,9,5,10,10 ... -- it returns 5,5,5,5,10,10 see man that's too redundant.. it would mean also to

DELETE FROM dbo.salesfact WHERE salesuniqueid IN
(
5,5,5,5,10,1 )

hummmm.... I think there's something wrong with this....

"Wisdom is knowing what to do next, skill is knowing how to do it, and virtue is doing it. "

"The questions you ask consistently will create either enervation or enjoyment, indignation or inspiration, misery or magic. Ask the questions that will uplift your spirit and push you along the path of human excellence. "

K.I.S.S - Keep it simple stupid

raclede™

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-24 : 02:24:23

I think neither will do what pt_at_us wants

raclede yours doesnt work - see example below

dursaliye yours deletes the duplicates but i guess pt_at_us would like to leave one unduplicated row

pt_at_us, heres a couple of links about duplication
[url]http://www.sqlteam.com/item.asp?ItemID=3331[/url]
[url]http://www.databasejournal.com/features/mssql/article.php/1438651[/url]

Example


CREATE TABLE #SalesFact(SalesUniqueID int)
GO
INSERT #SalesFact(SalesUniqueID)
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 5 UNION ALL
SELECT 5 UNION ALL
SELECT 6
GO
--Duplicates 1,3,5

--raclede
BEGIN TRANSACTION raclede
DELETE FROM #SalesFact WHERE SalesUniqueID IN
(
SELECT COUNT(SalesUniqueID) AS SalesUniqueID
FROM #SalesFact
GROUP BY SalesUniqueID
HAVING (COUNT(SalesUniqueID) > 1)
)
SELECT * FROM #SalesFact
ROLLBACK TRANSACTION raclede

/*----Results-----
SalesUniqueID 
------------- 
1
1
4
5
5
5
6  */ 

--dursaliye
BEGIN TRANSACTION dursaliye
DELETE FROM #SalesFact WHERE SalesUniqueID IN
(
SELECT SalesUniqueID FROM #SalesFact t1
WHERE 1<(SELECT COUNT(SalesUniqueID) AS SalesUniqueID FROM #SalesFact t2 WHERE t2.SalesUniqueID = t1.SalesUniqueID)
)
SELECT * FROM #SalesFact
ROLLBACK TRANSACTION dursaliye

/*----Results-----
SalesUniqueID 
------------- 
2
4
6 */

Andy

Beauty is in the eyes of the beerholder

dursaliye
Starting Member

22 Posts

Posted - 2005-05-24 : 13:38:50

SET NOCOUNT ON
GO
SELECT * INTO #tempsalesuniqueid FROM
(
SELECT TOP 1 salesuniqueid FROM dbo.salesfact t1
WHERE 1<(SELECT COUNT(salesuniqueid) AS salesuniqueid FROM dbo.salesfact t2 WHERE t2.salesuniqueid = t1.salesuniqueid)
)
AS duplicates
GO
DELETE FROM WHERE salesuniqueid IN
(
SELECT TOP 1 salesuniqueid FROM dbo.salesfact t1
WHERE 1<(SELECT COUNT(salesuniqueid) AS salesuniqueid FROM dbo.salesfact t2 WHERE t2.salesuniqueid = t1.salesuniqueid)
)
GO
INSERT dbo.salesfact SELECT * FROM #tempsalesuniqueid
DROP TABLE #tempsalesuniqueid
GO
SELECT * FROM dbo.salesfact

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-24 : 17:13:07

That still doesnt work, that will just remove the 1st duplicate it finds, unless you put it into a loop.

Andy

Beauty is in the eyes of the beerholder

dursaliye
Starting Member

22 Posts

Posted - 2005-05-24 : 18:05:04

I know, he can do it with a WHILE loop instead of CURSOR

pt_at_us
Starting Member

2 Posts

Posted - 2005-05-25 : 23:30:50

Hi all,

its not just deleting the duplicate records from fact table. i have to validate the duplicate records and delete the non-valid record.
My procedure should check for differentiating columns for the duplicated records. suppose if the differentiating column(peoplesid in my procedure) is column of a dimension table(in my table its tdpeople) i need to check the currentrec_ind column flag('Y' or 'N') of the dimension table, pick up the peoplesid and delete the duplicate record in facttable which has this peoplesid value(i.e flag='N'). that is the reason why i have used if statements in the cursor loop.

iam not sure if i made it clear...but that is my requirement and iam still struggling..

thanks,
pt_at_us

dursaliye
Starting Member

22 Posts

Posted - 2005-05-26 : 02:30:57

I think, this should work

--------------------------------------------
CREATE PROCEDURE removeDuplicates @salunquid int, @pe_sid int, @pr_sid int, @salunquid1 int, @pe_sid1 int, @pr_sid1 int
AS
IF @salunquid = @salunquid1
BEGIN

--Compares the sid fields of duplicate records.
IF @pe_sid <> @pe_sid1
BEGIN
--Gets currec_ind flag FROM the dIFferentiator column.
SELECT @perec_ind = currec_ind FROM people WHERE peoplesid = @pe_sid
SELECT @perec_ind1 = currec_ind FROM people WHERE peoplesid = @pe_sid1

--Check for currec_ind column flag.
IF @perec_ind='N'
BEGIN
BEGIN TRANSACTION
--DELETEs the duplicate record FROM salesfact table.
DELETE FROM salesfact WHERE salesuniqueid = @salunquid AND peoplesid=@pe_sid;
COMMIT TRANSACTION
END

IF @perec_ind1='N'
BEGIN
BEGIN TRANSACTION
DELETE FROM salesfact WHERE salesuniqueid = @salunquid1 AND peoplesid=@pe_sid1;
COMMIT TRANSACTION
END
END
Else

IF @pr_sid <> @pr_sid1
BEGIN

SELECT @prrec_ind = currec_ind FROM product WHERE productsid = @pr_sid
SELECT @prrec_ind1 = currec_ind FROM product WHERE productsid = @pr_sid1

IF @prrec_ind='N'
BEGIN
BEGIN TRANSACTION
DELETE FROM salesfact WHERE salesuniqueid = @salunquid AND productsid=@pr_sid;
COMMIT TRANSACTION
END

IF @prrec_ind1='N'
BEGIN
BEGIN TRANSACTION
DELETE FROM salesfact WHERE salesuniqueid = @salunquid1 AND productsid=@pr_sid1;
COMMIT TRANSACTION
END
END

END
GO

SET NOCOUNT ON
GO
DECLARE @salunquid AS int
DECLARE @pe_sid AS int
DECLARE @pr_sid AS int
DECLARE @salunquid1 AS int
DECLARE @pe_sid1 AS int
DECLARE @pr_sid1 AS int

SELECT @salunquid = MIN(salesuniqueid) FROM tdpeople
WHILE @salunquid IS NOT NULL
BEGIN

SET @pe_sid= (SELECT peoplesid FROM tdpeople WHERE salesuniqueid= @salunquid)
SET @pr_sid = (SELECT productsid FROM tdpeople WHERE salesuniqueid= @salunquid)
SET @salunquid1 = (SELECT salesuniqueid FROM salesfact WHERE salesuniqueid= @salunquid)
SET @pe_sid1 = (SELECT peoplesid FROM salesfact WHERE salesuniqueid= @salunquid)
SET @pr_sid1 = (SELECT productsid FROM salesfact WHERE salesuniqueid= @salunquid)
EXEC removeDuplicates @salunquid , @pe_sid , @pr_sid, @salunquid1, @pe_sid1, @pr_sid1
SET @salunquid = (SELECT TOP 1 salesuniqueid FROM tdpeopled WHERE salesuniqueid > @salunquid )

END

GO

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-05-27 : 20:12:19

I'm too lazy to read all the code:

Alter table salesfact 
Add Rec_Id Int IDENTITY

Select salesuniqueid, max(Rec_Id) as Rec_Id  --or Min(Rec_Id) as Rec_Id
Into #sales
from salesfact

Delete
from salesfact
Where Rec_Id in 
        (
        Select Rec_ID
        from #Sales
        )

Deletes one (Min/Max) of the duplicates

You can't teach an old mouse new clicks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources