Code repeating number of times in a sp

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Code repeating number of times in a sp

Author

Topic

bpuccha
Starting Member

34 Posts

Posted - 2012-06-04 : 15:31:08

Hi,

In my stored procedure i have couple of same insert and update statements repeating multiple times.What is the best way to handle the code?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-04 : 16:42:03

You would need to show us for us to help you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-04 : 16:43:04

What is differing in those repeated code? is it filter condition?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bpuccha
Starting Member

34 Posts

Posted - 2012-06-05 : 12:35:56

My code will be like this

create #temptable(...)

declare @NumberRecords int
declare @Count int

insert into #temptable from table;

set @NumberRecords = @@identity
set @Count = 1
WHILE @Count <= @NumberRecords
begin
declare the variables;
--assign the values to the declared variable from the temp table
select ..... from #temptable where id = @Count

if (condition)
begin

if (condition)
begin

set of statements
end
else
begin
/* The below set of statements repeating multiple times in my code*/
insert into table1(...)
update table1 .....

end
SET @Count = @Count + 1
end
else
begin

insert into table1(...)
update table1 .....
SET @Count = @Count + 1
end
end

There is no filter condition in the repeated code

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 12:40:03

i cant understand need of loop here
why cant you implement it in set based way? what are you doing inside loop?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bpuccha
Starting Member

34 Posts

Posted - 2012-06-05 : 12:50:41

For each row in the #temptable I have to validate so many conditions and i have to look into same tables inside the while loop and insert the data accordingly to a table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 16:08:42

quote:
Originally posted by bpuccha

For each row in the #temptable I have to validate so many conditions and i have to look into same tables inside the while loop and insert the data accordingly to a table.

why cant you wrap it by using a join condition inside your select and then using it to insert to final table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources