Go4Expert

Go4Expert (http://www.go4expert.com/)
-   SQL Server (http://www.go4expert.com/forums/sql-server-forum/)
-   -   Stored Procedure (http://www.go4expert.com/forums/stored-procedure-t715/)

techlearner 13Apr2006 01:32

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

shabbir 13Apr2006 06:50

Re: Stored Procedure
 
Its good to see that you are willing to optimize your code and in my opinion
Quote:

Originally Posted by techlearner
1. Use an MSSQL stored procedure with all the validation checks and call in the event.

would be the fastest. This is because when you have the SP the database engine optimizes it for better performances. Read the Stored procedures and you will get more idea about the SP.

coderzone 13Apr2006 06:58

Re: Stored Procedure
 
Quote:

1. Use an MSSQL stored procedure with all the validation checks and call in the event.
Better performance.
Quote:

2. Use a VB global function with all the validation checks and call in the respected event.
Better maintainance of the VB Code
Quote:

3. Write the code directly in the event function as and when required.
None of the above.

techlearner 13Apr2006 18:27

Re: Stored Procedure
 
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.

aspguy 13Apr2006 20:04

Re: Stored Procedure
 
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.

techlearner 13Apr2006 21:32

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


All times are GMT +5.5. The time now is 21:28.