October 28, 2010 at 3:11 am
Hey guys
As one part of a stored procedure that I need to create, I need to DELETE rows that match certain criteria in tables that have been dynamically created. By "dynamically created" I mean that their names vary, e.g. "objs_MyLittlePony", "objs_BobbysSports" and "objs_WhyMULTICSWillRuleTheWorldAgain" As a worst case scenario, I am assuming that there are about 5000 of these tables - currently, the most any of our clients have is about 300.
The method that I am using now is as follows:
DECLARE tableNamesCursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
o.name
FROM
sys.objects o
where
o.type = 'U' AND
o.name like 'objs_%'
order by
o.name
OPEN tableNamesCursor
BEGIN TRY
FETCH NEXT FROM tableNamesCursor
INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = '
IF EXISTS (SELECT blah_ID FROM [dbo].[' + @tableName + '] WHERE blah_String = @blahString)
BEGIN
DELETE FROM
[dbo].[' + @tableName + ']
WHERE
blah_String = @blahString
END'
SET @paramlist = '@blahString VARCHAR(100)'
EXEC sp_executesql @sql,
@paramlist,
@blahString
FETCH NEXT FROM tableNamesCursor
INTO @tableName
END
CLOSE tableNamesCursor
DEALLOCATE tableNamesCursor
END TRY
BEGIN CATCH
CLOSE tableNamesCursor
DEALLOCATE tableNamesCursor
EXEC dba_Error_Handler @IsDynamicSQL = 1
END CATCH
Because the number of tables in a worst case scenario is relatively small at only 5000, unless a set-based approach would be easier to maintain than the cursor above I don't see any value in changing the above code. HOWEVER, I would like to grow my own knowledge and see if it can be done without using a cursor and a WHILE loop.
Perhaps a kludgy
SELECT
'IF EXISTS (SELECT blah_ID FROM [dbo].[' + o.name + '] WHERE blah_String = ' + @blahString + ')
BEGIN
DELETE FROM
[dbo].[' + @tableName + ']
WHERE
blah_String = ' + @blahString + '
END'
FROM
sys.objects o
where
o.type = 'U' AND
o.name like 'doc_%'
order by
o.name
would work - if I knew how to execute each line of that resultset.
Any ideas?
An interesting discussion on cursors - Cursors Be Gone! by By Gaby Abed. bob.willsie summed up that discussion nicely (to me anyway):
bob.willsie (12/24/2008)
As a grizzled old coot that is new to SQL, but very old to programming, it appears as though the real answer on which way is better is the same as it always has been:It depends...
Thanks
Andrew
October 28, 2010 at 3:34 am
How about this:
DECLARE @sql as varchar(max)
SET @sql = ''
SELECT @sql = @sql + char(13) + char(10) +
'IF EXISTS (SELECT blah_ID FROM [dbo].[' + o.name + '] WHERE blah_String = @blahString )
BEGIN
DELETE FROM
[dbo].[' + @tableName + ']
WHERE
blah_String = @blahString
END'
FROM
sys.objects o
where
o.type = 'U' AND
o.name like 'doc_%'
order by
o.name
EXECUTE sp_executesql @sql, @paramlist, @blahString
-- Gianluca Sartori
October 28, 2010 at 4:41 am
Thanks Gianluca, that looks like a neat solution.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply