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.
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.
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.
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.
