Stored Procedure flow | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Stored Procedure flow

Hello,
I have piece of progam that inserts the data of a new applicant which consists of three forms. i.e when the applicant finishes first form he is directed to second and then third form. I use stored procedure to handle the code. I have 2 problems here and : 1 – shall I make stored procedure for every form . I feel it is a lot to have 3 stored prcdures just to insert this info. (insertion module) and of course I will have another 3 for the update module and another 3 for the view module …. a lot. I have tried to use just one sp instead of 3 but was faced by the problem of parameters being different from one sp to another. i.e every form has different no. of parameters with different datatypes. 2 – I need the code of all of the 3 forms to be done as one whole transaction either to succeed or to fail as whole. I know that one sp may act as a transaction (rollback and commit trans ) but what if I used more than one sp to perform a certain work that acts as one unit. I have surfed the internet heavily to find an article that addreeses such issue but I have found nothing. Note: I use asp and Sql server 2000 A lot of thanks. Rasha Zaki
Web developer
Cairo, Egypt Rasha zaki
Web Developer
Cairo, Egypt
My advice is to have a stored procedures for each form. Each of these could insert into a staging table (one for each form). I imagine its feasible the user could just fail to fill out the whole form, and just browse away from your site. In this case you probably would not want to keep what hes entered, which is the reason for the staging tables.
Validate input before it goes into the staging table. Timestamp each record and periodically delete any records in the staging table which are over N hours old. If the user DOES complete the form, you can then invoke a fourth stored procedure, which transfers the three records (excluding the timestamps) from the staging tables into your live tables, in one transaction. That way you are not getting incomplete or invalid data into your live tables.
Actually, you could just have 2 staging tables (for the first two forms, and the 3rd procedure does the transfer, along with inserting the third forms data). Dont worry about 3 or 4 stored procedures for this, its really not a lot and makes for a nice clean design.
Hi
Thanks for your help.
I think I have reached a nice solution that solves up the problem of having alot of sp, s. I got the solution from the last paragraph of an article on the following link
http://www.winnetmag.com/SQLServer/Article/ArticleID/15663/15663.html
It says[/b]
Grouping Stored Procedures
Grouping stored procedures is one of those features that nobody seems to use, which makes it a likely exam topic. If you have several associated procedures, perhaps for the branching logic I discussed, you can group them by naming them with the same name and individual identification numbers, with a semicolon between the name and the number. The procedure names would look like procname;1, procname;2, and so on. The benefit of using this naming convention, apart from consistency, is that you can use a DROP PROCEDURE procname command to drop all the procedures at once. However, after you group the procedures, you can’t drop them individually. Stored procedures are a powerful part of SQL Server. They offer benefits in both security and performance. Any client application you use will almost certainly run better if you add some stored procedures running on the server. As for the staging tables, I am a little bit not convinced. I think this puts additional unessary work. Ther must be another way to solve this problem.
Thanks very much again. Rasha zaki
Web Developer
Cairo, Egypt
Another method which is simple is to retain the form elements when going from page to page. You can save the previous forms values in hidden form fields so they get moved along durring the process. Then on the last page you are only making one SQL/SP call. This eliminates the need for Session variables and more than one SP. You can still do validation on each page as they enter data and can provide a final preview of their entries on the last page before commital to the DB.
]]>