Is it better to write a more focused stored procedure with fewer parameters?

Question

Say I have a stored procedure that returns data from a SELECT query. I would like to get a slightly different reduction in these results depending on what parameters I skip. I am wondering if it is better to have several stored procedures that take one or no parameters for this (e.g. GetXByDate or GetXByUser) or one stored procedure with several parameters that make the lot (e.g. GetX)?

The advantage of the first option is that it is simpler and possibly faster, but the disadvantage is that the essence of the request is duplicated by stored procedures and must be supported in several places.

The advantage of the second option is that the request is present only once, but the disadvantage is that the request is more complex and difficult to troubleshoot.

What do you use in your decisions and why?

Answer 1

More complex stored procedures are more complex to compile the SQL server correctly and execute quickly and efficiently.

Even in a large stored procedure, you need to either have multiple copies of the request, or add a lot of CASE and IF to it, which reduces performance. Thus, you do not really get much from combining everything together.

In my personal experience, I also consider large SQL code with a lot of branches, which are more difficult to support with several smaller and simpler sprocs.

UDF .

, ( , , ), , sproc .

Answer 2

, . . , / , .

Answer 3

GetXByDate, GetXByUser,... , , , , , .

, , . GetAndProcessXByDate GetXByDate, GetXByUser,... ProcessX.

, , : ...:)

Answer 4

I @tvanfosson.

, : sproc (, GetX), sprocs (GetXY, GetXZ), , .

, , : , - GetXY, GetXZ.

.

Answer 5

, #, LinqToSQL, .

Answer 6

AJs . , sprocs, .

Sproc UDF sprocs.

Answer 7

/ ? SELECT, , , SELECT, , ..