Combining 2 sprocs into one, is it good code reuse?

Question

Combining 2 sprocs into one, is it good code reuse?

Simon Martin

SSC-Addicted

Points: 468
More actions
September 27, 2007 at 2:16 am

#153859

Rather than have to maintain 1 sproc to return full details of a category and another just to return the ID and title (which would be used in a DropDownList or navigation) I thought I would combine them into this single sproc. Does anyone see any problems with doing this, or could you suggest a better approach?
Or should I be using 2 sprocs?
CREATE PROCEDURE [dbo].[usp_Sel_Categories]
(
@categoryID INT = NULL
, @fullDetails BIT
)
AS
BEGIN
IF @fullDetails = 1
BEGIN
SELECT pkCategoryID, Title, PreAmble, BannerImg, PostAmble
FROM tbl_Categories
WHERE (pkCategory = @categoryID OR @categoryID IS NULL)
AND (IsDeleted = 0)
END
ELSE
BEGIN
SELECT pkCategoryID, Title
FROM tbl_Categories
WHERE (pkCategory = @categoryID OR @categoryID IS NULL)
AND (IsDeleted = 0)
END
END
Simon
Sharing knowledge saves valuable time!
Simon Martin

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Like everything else, it depends...

It's easier to maintain security differences on two procs if you only want certain people to be able to use the "full-details" version. If you combine them, then you have to do that bit of security in the proc.

Of course, the other answer is, that's what parameters are made for... lots of folks, including Microsoft, change the basic functionality of a proc by use of parameters.

Last, but not least, is there some reason that you may wish to keep the functionality separate? And, what are you going to break by changing from two procs to one or is this a new proc?

Except for the security consideration, I don't see a problem with combining these... it would probably be helpful to the next person to add some documentation in a header that identifies what the proc does and what parameters do what 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Simon Martin SSC-Addicted Points: 468 More actions · Answer 2

Hi Jeff,

Thanks for the reply. I'm going to implement this approach in a new project I've been assigned to. Its an internal thing and so I don't have to worry too much about security - just performance. I will post back if I find out anything useful.

Sharing knowledge saves valuable time!
Simon Martin

mengus Ten Centuries Points: 1187 More actions · Answer 3

Simon,

I agree with Jeff. Except for the security reasons, combining stored procedures depend on the requirements. However, why not return all columns? I would assume this will require Application changes; otherwise the lookups will not work.

In my development days, I would consider just using the ‘full details’ stored procedure, because the number of rows returned is relatively small for lookups. We handle lookups on the Business Objects layer along with a Data Tier and a mapping class.

There were times when there was a need to use more than two columns from the lookup result-sets. Example: using ProductID, BusinessType and ProductName

For the stored procedures, these can be generated in a number of seconds with CodeSmith.

Regards,

Wameng Vang

MCTS

Sergiy SSC Guru Points: 110208 More actions · Answer 4

I believe for filling dropdown you never need to filter by categoryID.

So I see here 2 SPs:

1st - without parameters and with 2 columns returned - for dropdown;

2nd - with @categoryID parameter and full recordset returned - for details pane.

_____________
Code for TallyGenerator