4

I have a stored procedure that deletes data from multiple tables. The SQL is all the same, except for the name of the table.

UPDATE desttable
SET desttable.reportarea = esz2reportarea.reportarea
FROM table1 AS desttable JOIN esz2reportarea ON desttable.subdivisn = esz2reportarea.esz
WHERE desttable.reportarea = ''

UPDATE desttable
SET desttable.reportarea = esz2reportarea.reportarea
FROM table2 AS desttable JOIN esz2reportarea ON desttable.subdivisn = esz2reportarea.esz
WHERE desttable.reportarea = ''

...

UPDATE desttable
SET desttable.reportarea = esz2reportarea.reportarea
FROM table10 AS desttable JOIN esz2reportarea ON desttable.subdivisn = esz2reportarea.esz
WHERE desttable.reportarea = ''

I'd like to rewrite this to use some kind of loop, so that the SQL is only written once. (This is for SQL Server 2008R2.)

The list of tables that need to be updated will remain static; if there are any changes to the list of tables to be updated, it would be due to an application upgrade, and I'd have to redetermine which tables need to be modified at that time. The list of tables to be updated will be placed into a temporary table.

SELECT * INTO #tablenames
FROM (VALUES ('table1'), ('table2'), ('table3')) AS tables(tablename)
  • Where do you get the list of table names from? This is similar to my answer here: dba.stackexchange.com/a/120877/30859 – Solomon Rutzky Dec 3 '15 at 18:19
  • The list of table names are not in a table. I suppose it's possible to create a variable or temp table that contains the table names. – Kevin M. Dec 3 '15 at 18:37
  • So, where IS the list of table names? – Aaron Bertrand Dec 3 '15 at 18:37
  • 1
    Where does that list from from ? Is it only in your head? Is it passed in by an application or called by another stored procedure? Will this list change over time or is it static? We need more info in order to provide a solution that will actual work. – Solomon Rutzky Dec 3 '15 at 19:15
  • 1
    Then it is probably fine to have that list in a table variable. If you can update the question with that, and with the text of your comment above (minus the "sorry, its in my head"), then the question will be answerable and @Kin's answer will have the proper context. – Solomon Rutzky Dec 3 '15 at 19:55
6

You dont need a loop for what you want. Dynamic SQL will help you want you want to achieve.

SELECT * INTO #tablenames
FROM (VALUES ('table1'), ('table2'), ('table3')) AS tables(tablename)

--select * from #tablenames



declare @sqltext nvarchar(max) = N''
select @sqltext += 'UPDATE  desttable
SET desttable.reportarea = esz2reportarea.reportarea
FROM '+quotename(tablename)+ ' table1 AS desttable JOIN esz2reportarea ON desttable.subdivisn = esz2reportarea.esz
WHERE desttable.reportarea = '''';'+char(10)
from #tablenames  
order by tablename

print @sqltext

-- once you review the above print statement and are HAPPY, uncomment the below part. 
-- Below part will actually RUN the UPDATE statement ... 
-- exec sp_executesql @sqltext

Note: I agree with @AaronBertrand that you should create a physical table - that will be a driver table for the update statement and just update the table when you want to modify or insert more values if you want other tables to be updated.

  • Given the specifics of what he's doing, a cursor through the table @table variable might be more prudent depending on how error handling should occur. It would allow any try-catch to be done outside of dynamic SQL ( unless of course he wants an all-or-nothing failure or conditional catch block ) – Peter Vandivier Dec 3 '15 at 22:20
  • @PeterVandivier Would be good to see your approach as well. It will add another approach to OP's answer. You can post it as another answer. – Kin Shah Dec 3 '15 at 23:22
1

At Kin's suggestion, please find a cursor method below. Hope this helps / provides another approach if needed!

set nocount on;

declare @UpdateList table
(
    tableName sysname unique,
    FailCondition int,
    orderBy int identity
);

insert @UpdateList 
(tableName,FailCondition)
values
('Table1',1),
('Table2',2),
('Table3',1),
('Table4',1);

declare
    @tableName sysname,
    @FailCondition int,
    @Sql nvarchar( max ),
    @ErrMsg nvarchar( max ) = '',
    @RetMsg nvarchar( max ) = '',
    @Lb char( 1 ) = char( 10 ),
    @Rows int;

declare UpdateCursor cursor for
    select tableName, FailCondition from @UpdateList order by orderBy;

open UpdateCursor;

fetch next from UpdateCursor into @tableName, @FailCondition;

begin tran;

while @@fetch_status = 0
begin
    set @Sql = 
'UPDATE desttable
SET desttable.reportarea = esz2reportarea.reportarea
FROM ' + @tableName + ' table1 AS desttable JOIN esz2reportarea ON desttable.subdivisn = esz2reportarea.esz
WHERE desttable.reportarea = '''';' 
-- optional logging output ( i probably would )
+ @Lb + 'set @Rows = @@rowcount;' 
;

    begin try
        exec sp_executesql @Sql, N'@Rows int output', @Rows = @Rows output;

        select @RetMsg += @tableName + ' updated SUCCESSFULLY. ' + convert( varchar, @Rows ) + ' row(s) affected';
    end try
    begin catch
        select @ErrMsg += error_message() + @Lb;

--for this example. fail condition 2 aborts all updates, fail condition 1 allows the updates to continue but notes the error

        if @FailCondition = 2 goto FailTran;
    end catch;
    fetch next from UpdateCursor into @tableName, @FailCondition;
end;

commit tran;

FailTran:
if @@trancount <> 0 
begin
    rollback tran;

    set @ErrMsg = 'Fail Condition 2 was reached. All updates ABORTED. ' + @Lb + @ErrMsg;

    raiserror( @ErrMsg, 11, -1 );
end;

close UpdateCursor;

deallocate UpdateCursor;

if @ErrMsg <> '' raiserror( @ErrMsg, 11, -1 );

print @RetMsg;
  • 1
    I don't know anything about cursors, but have been meaning to learn. I'm not sure that I will use this approach, but I will definitely study it to add to my SQL knowledge. Thank you! – Kevin M. Dec 4 '15 at 5:22
  • Gl,hf!! For the full ref there's always the gospel according to msdn, but the short version is it's a looping method than I think would be helpful for the situation you describe. – Peter Vandivier Dec 4 '15 at 6:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.