Split an existing Proc into two.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Split an existing Proc into two.

Author

Topic

rumblepup
Starting Member

14 Posts

Posted - 2007-05-24 : 16:27:46

I know, sounds weird, but I have an existing stored procedure that helps me create a navigational structure for my website.

Table has this structure:
uid - int
IsActive - Smallint
ParentLevel - int
ParentID - int
Name - nvarchar
Description - nvarchar

I added an additional column
DisplayOrder - int

And now I need to add another column named
Type - nvarchar

for a particular reason

The stored proc looks like this.

CREATE procedure hl_GetCategories
AS
SET NOCOUNT ON
SELECT [uid], [Name] FROM Categories WITH(NOLOCK) WHERE ParentLevel = 0 AND uid > 1 ORDER BY DisplayOrder
-- added the filter for uid > 1 so that we only get actual
-- categories and not the reserved blank one.
SELECT [uid],[ParentID],[Name] FROM Categories WITH(NOLOCK) WHERE ParentLevel = 1
-- we are only need the level that is one level deep
GO

And this grabs a list of Categories and 1 Child Level SubCategory that I use to create navigation.

However, my needs have kind of changed. I need to split this proc, or create two different procs to do something similiar.

I need a list of Categories and 1 Child-Level Down where
Type = MainCat (MainCat means a general category)

or
Type = BrandCat (BrandCat means a category with the item Brand Name)

So I can create two different navigational controls.

Any idea on how best to do this?

Kristen
Test

22859 Posts

Posted - 2007-05-25 : 03:05:28

"Any idea on how best to do this?"

Well, it sounds like you either need an Sproc that takes a parameter and does CASE A or CASE B depending on what the new parameter's value is. Or you make two different SProcs and your Application calls whichever one is appropriate.

It always scares me when I see WITH(NOLOCK) in SQL code ...

Kristen

rumblepup
Starting Member

14 Posts

Posted - 2007-05-25 : 16:09:21

That's what I was asking. What's the best way to take this sproc, and use it to split it into two sprocs

Kristen
Test

22859 Posts

Posted - 2007-05-26 : 03:22:38

A single Sproc that does

IF this
THEN that
ELSE the other

MAY cause the query optimiser to cache either "that" or "the other" depending on the very first invocation, and then when it gets the other scenario it will use a poor choice of cached query plan.

Having said that I have lots of conditional logic in my SProcs!

If its easier for your application to have a single SProc, with a parameter describing the action to be taken, it might be better for the SProc to do:

IF this
THEN EXEC SprocA
ELSE EXEC SprocB

and then the query plans for SprocA and SprocB can be separately cached, and be optimally efficient. But the code maintenance becomes a bit more of a bore!

Others may have different views

Kristen

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-26 : 03:32:50

quote:
Originally posted by Kristen
It always scares me when I see WITH(NOLOCK) in SQL code ...

don't like to get dirty?

www.elsasoft.org

rumblepup
Starting Member

14 Posts

Posted - 2007-05-30 : 14:23:08

I never get the email notifications.

I'm sorry for the confustion, but let me describe it another way. How can I take the example of this Sproc, and use it as a code base for TWO NEW PROCS. I need two different functions to run seperately, not within the same sproc, based on the conditions I have.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources