infinte loop - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

infinte loop

Author

Topic

ranganathanmca
Starting Member

15 Posts

Posted - 2011-03-08 : 06:57:58

Hi Guys.,
i am facing some problem stored procedure getting infinte loop

stored procedure :
PROCEDURE [dbo].[sp_Blackberrydata]

AS
Declare @Count int
Declare @Insertcount int
Declare @VendorID int
Declare @VendorName varchar(225)
Declare @EmpID varchar(225)
Declare @Provider varchar(225)
Declare @Datetime datetime
Declare @Fname varchar(225)

select @Datetime=getdate()
select @Provider='AT&T Mobile'
select @insertCount = 0
select @Count = count(FName) from tblAT_TMOBILEPROCESS with(nolock)

New:

select @EmpID = EmpID from tblEMPLOYEE with(nolock) where First_Name in (select fname from tblAT_TMOBILEPROCESS)

select @Fname=First_Name from tblEMPLOYEE with(nolock) where EmpID=@EmpID

select @VendorID=Vendor_ID from tblVENDORMASTER with(nolock) where Vendor_Name=@Provider

IF not exists (select * from tblVENDORMASTER with(nolock) where Vendor_Name=@Provider)
BEGIN
insert into tblVENDORMASTER(Vendor_Name)values(@Provider)
END

insert into tblBLACKBERRY(Vendor_ID,EmpID,FName,LName,WLessNumber,Totalchrge,MAccesschrge,FChrge,Airchrge,Addkbchrge,Msgchrge,EQCharge,LDchrge,RMchrge,otherMisChrge,TaxRegulator,Vediochrge,chrgecredit,Date)
select @VendorID,@EmpID,FNAME,LNAME,WirelessNumber,totalcurrentcharges,monthlyaccesscharges,fcharges,airtimecharges,AdditionalKBusagescharges,Messaging_charges,Eqcharges,LDCharges,RoamingCharges,OtherMiscellaneousCharges,TaxesSurchargesRegulator,VideoShareCallingUsageCharges,OtherChargesCredits,@Datetime
from tblAT_TMOBILEPROCESS

select @insertCount = @insertCount + 1

if @insertCount < @Count
begin
GOTO New
end

please give me some idea about how reslove this issue

ranganathan palanisamy

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-08 : 11:34:17

quote:

select @EmpID = EmpID from tblEMPLOYEE with(nolock) where First_Name in (select fname from tblAT_TMOBILEPROCESS)

select @Fname=First_Name from tblEMPLOYEE with(nolock) where EmpID=@EmpID

select @VendorID=Vendor_ID from tblVENDORMASTER with(nolock) where Vendor_Name=@Provider

Not seen in detailed but I feel the logic is wrong.

It may not be going in infinite loop but the same records is getting inserted through out the execution of SP.

If it is correct then the reason may be:

Assume the value for @Fname is Rahul. Now where you filtering the record. Second time also you will get the same record and it will go in loop.

If my understanding is wrong then please feel free to correct me.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-03-08 : 12:36:00

your whole mess of sql statements will be able to be compressed into 1 INSERT statement. You are looping for no reason -- try thinking in sets rather than individual entries.

Can you give us some sample data for the tables involved? We'll be able to show you an insert statement that is many, many times more performant.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2011-03-09 : 06:35:43

use go at the end

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2011-03-09 : 06:36:39

r u getting any error

ranganathanmca
Starting Member

15 Posts

Posted - 2011-03-10 : 06:15:09

Thanks for your time guys.,i got solution please look below query

WITH Managers AS

(

--initialization

SELECT EmpID,supervisor_ID

FROM tblEmployee

WHERE empid = '286593'

UNION ALL

--recursive execution

SELECT e.empid,e.supervisor_ID

FROM tblEmployee e INNER JOIN Managers m

ON e.supervisor_ID = m.empid

WHERE e.empid <> '286593'

)

select * from managers

Thanks

ranganathan palanisamy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources