ansaurus

Question

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

Answer 1

A:

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.

astander 2009-12-01 19:17:26

astander, check my comment to Josh. You say use specialized parameterized queries, but don't you have to declare each parameter with its' type before you call the query? Can you dynamically declare the parameters?

eych 2009-12-02 15:32:51

You can add parameters by just specifying the name and value. SqlCommand cmd = new SqlCommand(); cmd.Parameters.Add("name", value); In VS 2008 they want you to use Parameters.AddWithValue

astander 2009-12-02 15:40:57

Answer 2

+1 A:

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.

Josh Yeager 2009-12-01 19:22:53

even if you create a dynamic Insert with parameters [e.g. "Insert into Customers (Name, City) Values (@pName ,@pCity)] and then add the parameters ["cmd.Paramters.Add(new SqlParameter("@CustomerId", System.Data.SqlDbType.Int))"], don't you still need to declare the parameter variables at the top?

eych 2009-12-02 15:14:26

It depends on what langauge/framework you are using. Your example looks like C#/ADO.NET. In that case, ADO.NET declares the parameters for you when you add them to the command's Parameters collection. One note: the parameter names in your query must match the names in the Parameters collection. So your code should look like this:SQL: Insert into Customers (Name, City) Values (@pName ,@pCity)C#: cmd.Parameters.AddWithValue("@pName", "John Doe"); cmd.Parameters.AddWithValue("@pCity", "Baltimore");The data type isn't usually needed for parameters.

Josh Yeager 2009-12-02 16:14:55

Answer 3

+1 A:

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

Paul Sasik 2009-12-01 19:26:55

Answer 4

+1 A:

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)

Jonas Elfström 2009-12-01 19:32:00

ansaurus

tags:

views:

answers:

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

related questions