SQL for loop using IDs returned in Select statement

Question

I need to delete a bunch of rows from my database under a certain condition. I have the select statement to return this condition and then for each row in this select statement I need it execute an SP that will delete these rows and a load of related rows. So far I have:

select importfileid from import.importfiles where importfilestatusid < 7

and then execute

EXEC    [import].[spDeleteFromAllImportRelatedTables]
    @fileID = @importfileid

I'm just not sure how to do the inbetween? If someone could point me in the right direction that would be great.

NB: This is a once off thing. Performance is not an issue.

Matten · Accepted Answer · 2012-01-05 10:54:45Z

2

you can use a cursor:

declare @importFileID int
declare @cur cursor

set @cur = cursor fast_forward for 
select importfileid from import.importfiles where importfilestatusid < 7

open @cur
fetch next from @cur into @importFileID
while(@@fetch_status = 0)
begin
  exec [import].[spDeleteFromAllImportRelatedTables] @fileID = @importFileID
  fetch next from @cur into @importFileID
end
close @cur
deallocate @cur

edited Jan 5, 2012 at 10:54

answered Jan 5, 2012 at 10:46

Matten

17.6k2 gold badges44 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ediblecode Over a year ago

Thank you this was perfect. I didn't know about cursors and would probably have selected each time I deleted one. I will have to read into these. NB: = not ==. +1

Matten Over a year ago

of course = and not ==, I am always forgetting it if I don't use sql for a short time :)

ikirachen · Accepted Answer · 2012-01-05 10:46:26Z

1

Try using cursor on the select . Look in this link http://msdn.microsoft.com/en-us/library/ms180169.aspx

answered Jan 5, 2012 at 10:46

ikirachen

777 bronze badges

Comments

Nilesh Thakkar · Accepted Answer · 2012-01-05 10:48:49Z

0

I'll never suggest to use cursor, rather go for SET based approach. if possible, you could use joins on all relative tables to delete all those rows. That would be faster approach. Create one SP and Create one Job that does this for you on specific interval, life will be easy.

answered Jan 5, 2012 at 10:48

Nilesh Thakkar

2,8951 gold badge25 silver badges44 bronze badges

3 Comments

Matten Over a year ago

as the op just wants to do this once or from time to time, nothing speaks again a cursor, as fast isn't really important...

Nilesh Thakkar Over a year ago

@Matten you're right, but I'm just sharing my point of view to solve the problem. I should have said in different way, no offense.

Matten Over a year ago

I totally agree with you, set-based operations are always (magnitudes) faster than these relying on procedural programming in t-sql.

MatBailie · Accepted Answer · 2012-01-05 10:50:33Z

You have several options, none of which are pretty.

Use a Cursor

With a (fastforward readonly) cursor, you cn loop through your results one at a time and call your stored procedure.

Holding Table

Insert all the records into a table, then record the stored procedure to read from that table.

This can be a real table, or a #temporary_table.

This has an advantage in that you can do all of the deletes together, rather thanin an iterative loop.

Encapsulate the first step in a function

If you put your SELECT code into a function, you can re-code the SP to call that function to find the records to be deleted. The parameters of the SP then become whatever the parameters of the Function are.

This is tidied up somewhat in SQL Server 2008+ with table parameters. But not in SQL Server 2005.

Richard Hanley · Accepted Answer · 2012-01-05 10:58:26Z

-1

I assume the SP is doing something more special than just deleting a row.

If this is a one off thing could you just do something hacky like:

SELECT 'EXEC    [import].[spDeleteFromAllImportRelatedTables]    @fileID =',  importfileid from import.importfiles where importfilestatusid < 7

And then copy and paste the results and run that?

Alternativly if this is a scheduled clean up consider re-thinking your code and write a new SP which does what DeleteFromAllImportRelatedTables does but using the criteria you stated above. Although there maybe some duplication of code it would likely make it quicker and easier to understand what is going on and easier to debug as you wouldn't be tracing through SP calls within an SP to find any issues.

edited Jan 5, 2012 at 10:58

answered Jan 5, 2012 at 10:47

Richard Hanley

3281 silver badge10 bronze badges

Collectives™ on Stack Overflow

SQL for loop using IDs returned in Select statement

5 Answers 5

2 Comments

Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related