Call SQL Stored Procedure Asyncronously

Hello,

I have a C# web page that calls a stored procedure. The page passes few parameters to the stored procedure and call it. The stored procedure does so many time consuming tasks on a huge number of database records but does not return any value.

Since the page is not expecting any return from the stored procedure, I want to execute the stored procedure asyncronously so that the user can continue working on the web page and other web pages while the stored procedure is running in the background. Also, I do not want the web server processes to be busy with the running stored procedure.

Any help, please.

Best regards,

Imran

Re: Call SQL Stored Procedure Asyncronously

you can initiate a callback to do that.

check this link

http://www.simple-talk.com/dotnet/asp.net/asynchronous-client-script-callbacks/

Re: Call SQL Stored Procedure Asyncronously

Thanks Sansan. But this is not what I need. Read the scenario carefully

Re: Call SQL Stored Procedure Asyncronously

are you passing any input parameters, if not then why not schedule the job within your DB. If you are passing parameters then brief your requirements plz.

Re: Call SQL Stored Procedure Asyncronously

Hi tanatrajan,

The application includes some calculations of items. The user has a button to re-calculate an item. When he clicks the button, the page will call a stored procedure that will insert a record in a table with re-calculation requests. Then the same stored proceedrue will call another stored procedure to process re-calculating the items in the table one by one. Something like the following

ALTER Procedure [re_calculate_item] (@item_id int, @requested_by varchar(50) = null)
as


insert into t_item_recalculate (item_id, requested_by, requested_on, status)
values (@item_id, @requested_by, getdate(), 1)


exec re_calculate_pending

re_calcualte_pending is the stored procedure that does a huge amount of processing that consumes time. But it does not return any value, it only updates few tables with re-calculated values.

Re: Call SQL Stored Procedure Asyncronously

Any help, please.

Re: Call SQL Stored Procedure Asyncronously

i) Threading.
ii) Open that page from where processing start in another window.

iii) I am quite hopefull about this method.
schedule the job,according to the requirment,data and trafiic u can schedule the time frequency.
See,User open that page,input some data and submit.Your store procedure start working.
Here don't call ur SP rather store the input from individual user in seperate table along with suitable id.
Structure of new table (id,parameter1,parameter1 from user.......... and status (bit).

And in ur SP,make some changes.Like The Sp will pick all record whose status=1
Cursor or while loop
Do its work as usual
and finally update the new table status=0.

Again pick next id and so on.

On fix time job will start automatically.
and user can continue doing its job.
It has ceratin benefit also,application will become fast.and so on.
Only thing is I am not sure about is Frequency Of that Page .
How frequently that page is use ?

any problem ask.