April 4, 2014 at 12:10 am
Hello all,
I have an issue with Delete statement.
In the code given below (its a part of actual proc),
if we use TRUNCATE to clean the temp tables, everything goes fine.
But if I use DELETE in place of truncate, system skips the IF loop 'if (@script_type = 1 OR @script_type = 2)'
I am not able to understand this behavioral difference between DELETE and TRUNCATE.
Recently the database is being used for replication, but that should not be a reason.
SELECT @max_rows = COUNT('X') FROM #temp_table1
SET@row_cnt = 1
WHILE @row_cnt <= @max_rows
BEGIN
select @field1 = field1,@field2 = field2,@field3 = field3 from#temp_table1
whererow_num = @row_cnt
insert into #temp_table3(.....) select .....
SELECT @max_copy_rows = COUNT('X') FROM #temp_table3
WHILE @copy_row_cnt <= @max_copy_rows
BEGIN
select@copy_field1 = field1 from#temp_table3 whererow_num = @copy_row_cnt
if (@script_type = 1 OR @script_type = 2)
begin
set @sql = .....
end
else
begin
set @sql = .....
end
truncate table dbo.#temp_table2
set @column_sql = .....
exec(@column_sql)
set @copy_row_cnt = @copy_row_cnt + 1
END
SET @row_cnt = @row_cnt + 1
truncate table #temp_table3
END
April 4, 2014 at 8:27 am
You need to supply a complete script that will display your problem.
I've changed the code you provided so it will run without initial errors, but this code does NOT have the problem you describe. In both cases (using the DELETE and using the TRUNCATE) the IF block will be executed.
declare @max_rows int, @max_copy_rows int
declare @row_cnt int, @copy_row_cnt int
declare @field1 int, @field2 char(1), @field3 char(1)
declare @copy_field1 int, @copy_field2 char(1), @copy_field3 char(1)
declare @script_type int
declare @sql nvarchar(500), @column_sql nvarchar(500)
create table #temp_table1 (field1 int, field2 char(1), field3 char(1), row_num int identity(1,1))
create table #temp_table2 (field1 int, field2 char(1), field3 char(1), row_num int)
create table #temp_table3 (field1 int, field2 char(1), field3 char(1), row_num int)
insert into #temp_table1 (field1, field2, field3) values(1, 'a', 'a'), (3, 'c', 'c'), (2, 'b', 'b')
SELECT @max_rows = COUNT('X') FROM #temp_table1
SET @row_cnt = 1
SET @copy_row_cnt = 1
WHILE @row_cnt <= @max_rows
BEGIN
select @field1 = field1,@field2 = field2,@field3 = field3 from #temp_table1
where row_num = @row_cnt
insert into #temp_table3
select * from #temp_table1
SELECT @max_copy_rows = COUNT('X') FROM #temp_table3
WHILE @copy_row_cnt <= @max_copy_rows
BEGIN
print 'x'
select @copy_field1 = field1 from #temp_table3 where row_num = @copy_row_cnt
if (@script_type = 1 OR @script_type = 2)
begin
set @sql = 'type 1 or 2'
print @sql
end
else
begin
set @sql = 'other type'
print @sql
end
truncate table dbo.#temp_table2
--delete from #temp_table2
set @column_sql = 'select * from #temp_table2'
exec(@column_sql)
set @copy_row_cnt = @copy_row_cnt + 1
END
SET @row_cnt = @row_cnt + 1
truncate table #temp_table3
--delete from #temp_table3
END
drop table #temp_table1
drop table #temp_table2
drop table #temp_table3
April 4, 2014 at 8:37 am
Why all the looping and dynamic sql to create inserts? From what you posted this looks procedural instead of set based. If you want some help to make this whole process better post some more details and I bet this can be made a lot more efficient.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply