Stored Procedure

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

  1. techlearner

    techlearner New Member

    Joined:
    Apr 6, 2006
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    0
    Hello,

    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

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    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

    Joined:
    Jul 25, 2004
    Messages:
    736
    Likes Received:
    38
    Trophy Points:
    28
    Better performance.
    Better maintainance of the VB Code
    None of the above.
     
  4. techlearner

    techlearner New Member

    Joined:
    Apr 6, 2006
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    0
    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

    Joined:
    May 2, 2005
    Messages:
    58
    Likes Received:
    1
    Trophy Points:
    0
    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

    Joined:
    Apr 6, 2006
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    0
    Thanks ASPGUY for the answering my question. I got your point.
    Thank You very much.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice