How to perform an action on each of a list of tables

My company's database has over a hundred files which contain the column "CoreID".  I want to create a SP that will delete all records for a certain value of CoreID.

I'm guessing I should use a cursor, but I can't find a good example of exactly what I'm trying to do.  First, I have an SQL statement that will give me the names of all files which have the CoreID column.  For each of those files, I want to DELETE FROM <filename> WHERE CoreID = <some value>

What should my SQL look like?
LVL 1
FrancineTaylorAsked:
Who is Participating?
 
JestersGrindConnect With a Mentor Commented:
You could do something like the code that I listed below.  Or if the tables are fairly static, meaning you don't add new tables with a CoreID column regularly, you could use your select statement to generate the delete statements and then use them in the stored procedure.

Greg


DECLARE @SQL VARCHAR(8000), @Count INTEGER = 1, @TableName VARCHAR(100), @CoreID INTEGER

DECLARE @Tables TABLE(ID INTEGER IDENTITY(1,1), TableName VARCHAR(100))

INSERT INTO @Tables (TableName)
<Your Select Statement>

WHILE @Count <= (SELECT MAX(ID) FROM @Tables)

BEGIN

	SELECT @TableName = TableName FROM @Tables WHERE ID = @Count
	
	SELECT @SQL = 'DELETE FROM ' + @TableName + 'WHERE CoreID = ' + CONVERT(VARCHAR(10), @CoreID)
	
	EXECUTE sp_executesql @SQL
	
	SET @Count += 1
	
END

Open in new window

0
 
FrancineTaylorAuthor Commented:
Thanks, it was the creation and execution of the command that was the part I was missing.

I guess you can do it with a temp table or a cursor...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.