1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

Stored Procedure

Discussion in 'SQL Server' started by techlearner, Apr 12, 2006.

  1. techlearner

    techlearner New Member


    I am working on a VB/SQL application. I have to insert records from VB forms to MS sql tables which are linked via ADO. Considering the constraints of the fields and around 36 total fields in the tables, I am not sure what would be the best and optimal option:-
    1. Use an MSSQL stored procedure with all the validation checks and call in the event.
    2. Use a VB global function with all the validation checks and call in the respected event.
    3. Write the code directly in the event function as and when required.
    I would appreciate it if someone can help with my problem.

    Thanks in advance.
  2. shabbir

    shabbir Administrator Staff Member

    Its good to see that you are willing to optimize your code and in my opinion
    would be the fastest. This is because when you have the SP the database engine optimizes it for better performances. Read the [thread=329]Stored procedures[/thread] and you will get more idea about the SP.
  3. coderzone

    coderzone Super Moderator

    Better performance.
    Better maintainance of the VB Code
    None of the above.
  4. techlearner

    techlearner New Member

    Thanks Shabbir and Coderzone for your replies. The only concern I had with using Stored Procedures was the number of arguments(which could be very large) I would have to pass to it through the VB GUI.
    Since some of my SQL Server tables have more than 40 fields, I would have to pass about the same or even more number of arguments to the stored procedure for insert/update in these tables.These arguments would come from VB GUI forms through user inputs, So in short, is it ok to pass that many arguments to SQL Server Stored Procedure from VB front-end without compromising the effeciency? Thanks in advance.
  5. aspguy

    aspguy New Member

    Yes thats not a problem.

    The only thing you should keep in mind is after passing 40 arguments it should not be one single query to be executed. What I mean is you have validation and all the other things in SP and not at Front end.
  6. techlearner

    techlearner New Member

    Thanks ASPGUY for the answering my question. I got your point.
    Thank You very much.

Share This Page