I need to rebuild an old huge website and probably to port everyting to ASP.NET and jQuery and I would like to ask for some suggestion and tips. Actually the website uses:

  • Ajax (client site with prototype.js)
  • ASP (vb script server side)
  • SQL Server 2005
  • IIS 7 as web server

This website uses hundred of stored procedures and the requests are made by an ajax call and only 1 ASP page that contain an huge select case

Shortly an example:


var data = {
    action:     'NEWS',
    callback:   'doNews',
    param1:     $('text_example').value,
    ......:     ..........};
AjaxGet(data); // perform a call using another function + prototype


<% ......
select case request("Action")
case "NEWS"
    With cmmDB
        .ActiveConnection = Conn
        .CommandText = "sp_NEWS_TO_CALL_for_example"
        .CommandType = adCmdStoredProc 

        Set par0DB = .CreateParameter("Param1", adVarchar, adParamInput,6)
        Set par1DB = .CreateParameter(".....", adInteger, adParamInput)
        ' ........ ' can be more parameters

        .Parameters.Append par0DB
        .Parameters.Append par1DB

        par0DB.Value = request("Param1")
        par1DB.Value = request(".....")

        set rs=cmmDB.execute

        RecodsetToJSON rs, jsa ' create JSON response using a sub
    End With
.... %>

So as you can see I have an ASP page that has a lot of CASE and this page answers to all the ajax request in the site.

My question are:

  1. Instead of having many CASES is it possible to create dynamic vb code that parses the ajax request and creates dynamically the call to the desired SP (also implementing the parameters passed by JS)?
  2. What is the best approach to handle situations like this, by using the advantages of .Net + protoype or jQuery?
  3. How the big sites handle situation like this? Do they do it by creating 1 page for request?

Thanks in advance for suggestion, direction and tips.

  • How would re-building this site help the business?
    – NoChance
    Commented Sep 19, 2012 at 20:17

3 Answers 3


Your best bet is to totally forget the stored procedures, and build a standards complient .net app. I say it that way because I am not familiar with .NET, but it is an excellent web framework and is designed around what is loosely known as a MVC structure.

In MVC you separate your logic in three distinct components. The view which handles display like HTML/CSS. The AJAX blurs this a bit, but AJAX requests themselves should also follow an MVC structure. The controller is where requests are sent. A controller should handle a distinct request (i.e. page request). This is where your current site is using a single point of entry which is very complex. The MVC framework will have a router component that routes the requests to the respective controller. The last part is the model or database. This will be classes that map to your database. The queries will be either embedded as methods in the model or in the controller or a mix of both (rails uses a blend of both).

You would only put something in a stored procedure if it is especially complex (which can often still be done in the model), or it needs to do special handling in the db like triggers etc. I have written some extremely complex web apps and have used stored procedures in only one of them, and in that case it was to implement some legacy data security method.

You have a lot of work ahead of you since the database was probably not designed around this style. Don't be afraid to build views to fix some cases where the database doesn't map cleanly to your models. If you can pull it off redesigning the database may be needed with a migration over to the new model. That will depend on your cutover plan and if you have other systems that access the database. The reverse view could help you with that as well. In that case you build your new tables and make a view for the legacy apps.

Sorry I was so long winded.

  • thanks for your answer and i will try to follow also your suggestions, do you have a link for example where i can study and learn more about those concept ? this will help me to get the right direction. Commented Jul 23, 2012 at 8:26
  1. Instead to have many CASES is possible to create dynamic vb code that parse the ajax request and create dynamically the call to the desired SP (also implementing the parameters passed by JS)?

Yes, the "action" parameter should just contain the name of the SP. Then you can just delete all hundreds of switch cases and if.

  1. Or what is the best approach to handle situation like this, by using the advantages of .Net + protoype or jQuery ?

You don't even need all those libraries, I've implemented the exact same thing with just a few lines of JS and a single ashx generic handler. Best approach depends on your exact needs.

  1. How the big sites handle situation like this ? by creating 1 page for request ?

Each "big sites" have implemented their solution around their specific needs.

  • thanks for your answer and about the point 1, yes can use the action as SP name, but what about the parameters ? one ajax request can have 2 parameters, another can have 10 parameters, and what about the types of parameters (string, integer and lengths) ? you embeded in the ajax request the params name, type and lenghts ? Commented Jul 17, 2012 at 15:47
  • yes, if you use a flexible format like json, then you can put meta data like type and number of parameters. Then in your code you simply create a single function that loops through each parameter and "pulls" out the correct type.
    – Darknight
    Commented Jul 17, 2012 at 19:02

Overall, I think this question is too generic to have an "answer", but here is my "opinion" as a first ditch effort which doesn't reflect even a great approach.

I see this post is a bit old so hopefully by now, the OP has obtained a bit more ASP.NET as well as Object-Oriented Programming experience. With that, I would recommend forgetting about the stored procedures for a bit and focus on adding OOP principals to the web application initially. With the assumption that the use of ajax is necessary, I'd say the existing approach can be salvaged and to officially promote the use of the parsing ASP page to be an "engine" implemented as a service (think web/WCF service). Perhaps the service is responsible for parsing or it delegates parsing to a stored procedure or to a .NET object; both have their pros and cons: updating the parsing logic in a stored procedure would not require recompiling/deploying the application and could be delegated to a DBA (who could further optimize the routine), however, storing the logic in code would better suit versioning requirements of the application as the parser and code that uses the parser are likely stored together.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.