June 3, 2009 at 6:47 am
If I understand the code and the explanation, I think you can change this to use a single pass through one cursor. Instead of a set of SELECT statements and a set of DELETE statements, you need to take the WHERE clause of the SELECT statements and simply make it part of the WHERE clause in the DELETE statement. That makes it, at least for any one table, into a set based operation. You'll still need to build it dynamically since you're building a list of tables from the cursor. But it ought to work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 4, 2009 at 8:20 pm
[font="Verdana"]I haven't been able to test my code, since you didn't supply any table definitions or example data. However, here's the approach. Hopefully you can get an idea from this.
Note, I did use a loop! I think it would be possible to eliminate even the outer loop, but you'd just end up with a really huge dynamic SQL statement, so I don't really see the value in it.
declare
@sql nvarchar(500),
@tablename varchar(51),
@linktype varchar(51);
-- Create temptables used to store and retrieve info
-- #temptable used to store all the info regarding the invalid customform data
create table #temptable (id int, fileid int, tablename varchar(51), linktype varchar(51), type varchar(51));
--
-- loop through the tables an pull back the mismatched fields
--
set @tablename = '';
while (1=1) begin
--
-- fetch the next table name to use, along with the correct link type for that table
--
select top 1
@tablename = tablename,
@linktype = linktype
from customforms
where tablename > @tablename and
filetype = 100
order by
tablename;
--
-- if there are no more tables to process, then exit
--
if (@@rowcount = 0) break;
--
-- generate dynamic SQL to pull back the list of mis-matched types in the table
--
set @sql =
'select ' + @tablename + '.id, ' + @tablename + '.fileid, ' +
'@tablename as tablename, @linktype as linktype, properties.type ' +
'from ' + @tablename + ' join properties on ' + @tablename + '.fileid = properties.id ' +
'where properties.type @linktype';
--
-- save the list
--
insert into #temptable
exec sp_executesql
@stmt = @sql,
@params = N'@tablename varchar(51), @linktype varchar(51)',
@tablename = @tablename,
@linktype = @linktype;
end; -- while
--
-- generate the list of delete statements to clear the mis-matched link types
--
select 'delete from ' + tablename + ' where id = ' + cast(id as nvarchar(10)) + ';' from #temptable;
--
-- now show the raw data
--
select * from #temptable;
--
-- clean up
--
drop table #temptable;
Edited to clean up my code commentary.
[/font]
June 4, 2009 at 8:32 pm
I ended up semi-figuring this out myself last night, thanks for the replies. I still use a cursor and agree that coding this to use no loops at all is really acedemic and pretty counter-intuitive as far as efficiency goes.
Thanks for all the replies
declare @cursor cursor
declare @sql nvarchar(500)
declare @id int
declare @fileid int
declare @tablename varchar(51)
declare @linktype varchar(51)
declare @type varchar(51)
create table #deletequeries (qtext nvarchar(1000))
set @cursor = cursor for select tablename, linktype from customforms where filetype = 100
open @cursor
fetch next from @cursor into @tablename, @linktype
while @@fetch_status = 0
begin
set @sql = 'delete from ' + @tablename + ' where id in (select ' + @tablename + '.id from '
+ @tablename + ' join properties on ' + @tablename + '.fileid = properties.id and ' +
'properties.type ''' + @linktype + ''')'
insert #deletequeries values (@sql)
fetch next from @cursor into @tablename, @linktype
end
close @cursor
deallocate @cursor
select * from #deletequeries
drop table #deletequeries
June 4, 2009 at 8:36 pm
dlee (6/4/2009)
I ended up semi-figuring this out myself last night, thanks for the replies.
[font="Verdana"]Always a good thing! I don't know about you, but I usually learn more when I have to work through it myself.[/font]
dlee (6/4/2009)
I still use a cursor and agree that coding this to use no loops at all is really acedemic and pretty counter-intuitive as far as efficiency goes.
[font="Verdana"]I find the cursor syntax painful, and creating a loop without the cursor is easy enough. But maybe that's just me.[/font]
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply