quote:

---

This stored procedure reads data from a text file row by row and passes it through numerous validations before updating it. Since the volume of rows in the file is huge, the stored proc takes upto 45 minutes or more to execute.

---

CREATE PROCEDURE Upload_BankBranch_Details @UserID varchar(15),  @UploadID int, @SuccessCount int output, @FailureCount int output
 AS 

--********************Open cursor on staging table which contains recs bcp ed  from txt file ******

Declare Cur_UplBankBranch CURSOR 
FOR 
select 
ULBB_BM_Code,  ULBB_BBrM_Code,  ULBB_BBrM_Name,ULBB_Reason  
from 
	CMS_UploadLog_BankBranchMaster

open Cur_UplBankBranch

fetch next from Cur_UplBankBranch 
into @tmpBankCode,  @tmpBranchCode,  @tmpBranchName, @tmpReason

while @@FETCH_STATUS = 0
--***********************all validations checked***************** 

--******************** insert into final table******
	else
   		 begin
   			insert into 
CMS_BankBranchMaster(BBrM_BM_Code,  BBrM_Code, BBrM_Name,) 
			values 
(@tmpBankCode,  @tmpBranchCode,  @tmpBranchName)

--********************Also,insert current user and branch code to permission table     ******

  			insert into 
				CMS_User_Branch_Mapping 
			values 
(@UserID, @tmpBankCode,  @tmpBranchCode)

--********************Also,insert all users who are designated as 'SuperUsers' and ******
--********************branch code to permission table (using cursor)      ******

   			Declare Cur_UpdtSuperUser CURSOR 
   			FOR 
   				select UM_UserID from cms_groupmaster,cms_usermaster  
where UM_UserID <> @UserID and UM_GM_Code=Gm_Code and GM_Description='Superuser'
   				
			open Cur_UpdtSuperUser
  			fetch next from Cur_UpdtSuperUser INTO @superuser
	
   			while @@FETCH_STATUS = 0
    			 begin
insert into CMS_User_Branch_Mapping values (@superuser, @tmpBankCode,  @tmpBranchCode) 
    				fetch next from Cur_UpdtSuperUser INTO @superuser
    			 end
  			close Cur_UpdtSuperUser
   			deallocate Cur_UpdtSuperUser
--********************Update staging table record with Reason using original cursor with "where current of" ******
	update 
		CMS_UploadLog_BankBranchMaster 
	set 
		ULBB_Reason=@tmpReason 
		where current of  Cur_UplBankBranch 
 	fetch next 
  end

quote:

---

You should put the stored procedure into a job and use sp_start_job to run it. Then you can simply call sp_start_job from your ASP page. See Books Online for more on sp_start_job.

---

quote:

---

Sameer,

Regarding the sp_start_job, you can just execute that command from an ADO Connection Object (like oConn.Execute("sp_start_job ...")) and not need a stored procedure. The great thing about this is that once the job is started, then the "start_job" routine is done and returns to the page. It does not wait for the job to end, because all it checks is that the job got STARTED successfully.

As for your stored procedure, I would suggest you do away with the cursor if at all possible. Perhaps you could do a series of set-based commands, perhaps adding a STATUS field to the table and do something like this...

UPDATE Stagin SET STATUS = 'PENDING'
GO

UPDATE Staging SET STATUS = 'REJECT'
WHERE ... -- Put your test here like StartDate IS NULL, or ISNUMERIC(Field1) = 0, etc.

UPDATE Staging SET STATUS = 'REJECT'
WHERE ... -- Continue with next test, and so on...

Finally...
INSERT INTO FinalTable ... SELECT ... FROM Staging WHERE STATUS = 'PENDING'

Depending on your tests, these might be able to be merged together into fewer statements. To the degree that you can use set-based operations like this instead of cursors, you should see significant gains in performance.

---