create three stored procedures (select/update/insert) per table or as needed?

using asp.net, sql server 2008, winserver2003

we have about 1开发者_Python百科0 tables with about 20 fields each...

we have about 30 web forms where each form uses some variation of fields from some/all tables...

each form has its' own dataset depending on the tables it uses. However, to create that dataset, a Select * stored procedure is called on each used table. Dataset gets updated through the form/code, and a generic Insert or Update stored procedure is called per datatable, passing every field as a parameter. Although bloated, there exists only 3 stored procedures per table that are used by all forms, equaling 30 total stored procedures.

Is it preferred to create custom Select/Update/Insert stored procedures for each form using only the fields that the form needs...thus totaling 90 total stored procedures? That number could increase as forms increase...

---

Do the stored procedures have any special functionality, or do they just run basic hard-coded statements like "SELECT * FROM table"?

If they just run basic statements, then it's probably better to get rid of the stored procedures and use dynamic SQL. This will reduce the amount of maintenance work you have to do. The old performance concerns with dynamic SQL are no longer relevant because SQL Server's caching and compilation systems are so much better now. The security concerns are obsolete as well, because you can use parameterization in dynamic queries now.

---

You should reconsider implementing biz logic concepts so tightly between your UI and your database. You and your team should step back to analyze what types of business objects your system requires rather than mapping to the large number of screens. You can have CRUD SPs per biz object but a lot of your display stuff ought to be handled by a handful of view (unless your model is completely zany.)

---

If you really need a SP (security?) then you should at least consider using Merge. With Merge you get an built in upsert command.

"Upsert, what?":

update myTable set Col1=@col1, Col2=@col2 where ID=@ID
if @@rowcount = 0
insert into myTable (Col1, Col2) values (@col1, @col2)

---

Is it realy required to use the SP for this, or could you use paramterized queries rather. This will avoid the growth of SPs, and will reduce the traffic from the server for fields that are not required per form.

Use specialized queries to perform the select, insert and update statements, rather than a general SP to do these from multiple forms.