Avoiding 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

Avoiding Cursor

Author

Topic

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-07 : 16:33:55

This query uses a cursor to fetch a parameter and pass it to another Stored proc. Is there a straightforward way to do this without using a cursor?

declare @deleteunassigned int
declare cur_unassigned cursor for select distinct a.cust_cont_pk
from cust_cont a, cont_fold_ass b (NOLOCK)
where a.cust_cont_pk != b.CUST_CONT_PK
open cur_unassigned
fetch next from cur_unassigned into @deleteunassigned
while @@fetch_status = 0
begin
exec spDeleteCustContbypk @deleteunassigned
fetch next from cur_unassigned into @deleteunassigned
end
close cur_unassigned
deallocate cur_unassigned
GO

declare @deleteunassigned int
declare cur_unassigned
cursor for
SELECT DISTINCT a.cust_cont_pk
FROM cust_cont a,
cont_fold_ass b (NOLOCK)
WHERE a.cust_cont_pk != b.CUST_CONT_PK
open cur_unassigned
FETCH NEXT FROM cur_unassigned INTO @deleteunassigned
while @@fetch_status = 0
begin
exec spDeleteCustContbypk @deleteunassigned
FETCH NEXT FROM cur_unassigned INTO @deleteunassigned
end
close cur_unassigned
deallocate cur_unassigned
GO

Future guru in the making.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-07 : 17:00:34

If all you are doing is deleting all customers which does not exist in cont_fold_ass table, you can do it straightaway using Delete command.

Delete a
from cust_cont a left join cont_fold_ass b
on a.cust_cont_pk = b.CUST_CONT_PK
where b.CUST_CONT_PK is NULL

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-07 : 19:59:51

Yes, unfortunately there is more to it. The cust_cont table has referential constraints on multiple other tables. The stored procedure spDeleteCustContbyPK handles the delete. I suppose I could just modify the procedure to do the deletes. I think I have some ideas now of where to go with this at this point though, I will post again if I get stuck.

Future guru in the making.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources