MS SQL Server - How do you organise SPs in SQL Server DB?

Author

How do you organise SPs in SQL Server DB?

Norman Yuan

2005-04-28, 11:23 am

It is common that an SQL Server database contains hundreds or thousands
Stored Procedures. Is there a way do organise them well, for example, like
you organise dick files with folders? In EM, for each database, you simply
get a long list of all your SPs. It is just not helpful at all.

Sps, not like tables, change from time to time. Some get modified, some
cease functioning. Different versions of SPs that work for the same apps,
even there are some SPs with the same code and the different names (for
different apps, for example). It is so painful to tell what SP is for what
app (I have to use very long SP name to convey some message as to what the
SP might do).

Why MS does not see the pain, while it tell people to use SPs as much as
possible? Or is it just me not seeing the proper way of doing this business?

Keith Kratochvil

2005-04-28, 11:23 am

I like the fact that they (Microsoft) do not make us follow any specific
convention. This gives us (the DBAs, and developers) the opportunity to
create our own "standardized" way of naming the stored procedures. Everyone
probably has an opinion of how stored procedures should be named. A naming
convention that I feel makes sense is
usp_<action><table><description>

usp stands for User Stored Procedure.
Action could be SEL UPD DEL INS (continue the list as needed for your
environment).
Table refers to the table impacted. Of course this becomes more complicated
when you have stored procedures that might reference more than one table,
but this is where common sense comes in and you call it something that means
something to you or your team).
Description would be any additional information that would help describe the
stored procedure to the person browsing the list of stored procedures.

I have used this convention in systems that have thousands of stored
procedures and it worked well for us.

By the way, do you use source control to maintain your stored procedures?
If not you might want to consider it. If you create and modify the stored
procedures within Query Analyzer you have the option to save the file. Give
the file a descriptive name (like the name of the stored procedure) and
check it into your source code repository. When you need to make changes
check out the file, make the change (using Query Analyzer), and execute the
"create procedure" script to create (or re-create) the stored procedure in
the appropriate database. You would then save the file and check it back
into source control (with comments). This allows you to know what changed,
when it changed, who changed it, and why. You could also build a system to
automate the deployment of the stored procedures.

--
Keith

"Norman Yuan" <NotReal@NotReal.not> wrote in message
news:ewnuMRATFHA.3308@TK2MSFTNGP14.phx.gbl...
> It is common that an SQL Server database contains hundreds or thousands
> Stored Procedures. Is there a way do organise them well, for example, like
> you organise dick files with folders? In EM, for each database, you simply
> get a long list of all your SPs. It is just not helpful at all.
>
> Sps, not like tables, change from time to time. Some get modified, some
> cease functioning. Different versions of SPs that work for the same apps,
> even there are some SPs with the same code and the different names (for
> different apps, for example). It is so painful to tell what SP is for what
> app (I have to use very long SP name to convey some message as to what the
> SP might do).
>
> Why MS does not see the pain, while it tell people to use SPs as much as
> possible? Or is it just me not seeing the proper way of doing this
> business?
>
>

Richard Ding

2005-04-28, 11:23 am

One way to organize your sps is to prefix them with 3 letter initials
representing the sps function, like ACT indicating accounting, CUS for
customer, VOU for voucher, ORD for orders, etc. So you can sort sps by name
in Enterprise manager. Sps placed in the same group are named after their
business action, such as ACTReportEarnings, ORDPurgeInvalidOrder
s.

"Norman Yuan" <NotReal@NotReal.not> wrote in message
news:ewnuMRATFHA.3308@TK2MSFTNGP14.phx.gbl...
> It is common that an SQL Server database contains hundreds or thousands
> Stored Procedures. Is there a way do organise them well, for example, like
> you organise dick files with folders? In EM, for each database, you simply
> get a long list of all your SPs. It is just not helpful at all.
>
> Sps, not like tables, change from time to time. Some get modified, some
> cease functioning. Different versions of SPs that work for the same apps,
> even there are some SPs with the same code and the different names (for
> different apps, for example). It is so painful to tell what SP is for what
> app (I have to use very long SP name to convey some message as to what the
> SP might do).
>
> Why MS does not see the pain, while it tell people to use SPs as much as
> possible? Or is it just me not seeing the proper way of doing this
> business?
>
>

David Gugick

2005-04-28, 1:23 pm

Norman Yuan wrote:
> It is common that an SQL Server database contains hundreds or
> thousands Stored Procedures. Is there a way do organise them well,
> for example, like you organise dick files with folders? In EM, for
> each database, you simply get a long list of all your SPs. It is just
> not helpful at all.
>
> Sps, not like tables, change from time to time. Some get modified,
> some cease functioning. Different versions of SPs that work for the
> same apps, even there are some SPs with the same code and the
> different names (for different apps, for example). It is so painful
> to tell what SP is for what app (I have to use very long SP name to
> convey some message as to what the SP might do).
>
> Why MS does not see the pain, while it tell people to use SPs as much
> as possible? Or is it just me not seeing the proper way of doing this
> business?

SQL Server 2005 will address this using schemas. For SQL 2000, a prefix
is probably your best choice to do what you want.

--
David Gugick
Imceda Software
www.imceda.com

Sgt. Sausage

2005-04-28, 8:23 pm

"Richard Ding" <rding@acadian-asset.com> wrote in message
news:%23AfaX3ATFHA.2336@TK2MSFTNGP12.phx.gbl...
> One way to organize your sps is to prefix them with 3 letter initials
> representing the sps function, like ACT indicating accounting, CUS for
> customer, VOU for voucher, ORD for orders, etc. So you can sort sps by
> name in Enterprise manager. Sps placed in the same group are named after
> their business action, such as ACTReportEarnings, ORDPurgeInvalidOrder
s.

We use a multi-level prefix. e.g.

usp_CS_ACCT_<whatever> means:

user stored procedure, CodeSmith generated, for the
Acctounting subsystem. Another example:

usp_UI_SYS_Log<whatever> means:

user stored procedure, for the UI component of the
System logging function.

I hate it. I'd rather be able to organize within EM
using a folder metaphor as this is how we store the
source in our source control system.

Norman Yuan

2005-04-28, 8:23 pm

Glad to here that SQL 2005 does something on this, but sadly, my company may
be stuck with SQL 2K for a quite while (we just get SQL2K up last year,
don't see a reason to move to SQL2005 when it come out).

Some of my SPs have 2 or 3, or even 4 prefixes. I might follow Keith's
suggestion. Thanks for all replies.

"David Gugick" <davidg-nospam@imceda.com> wrote in message
news:OFQsbEBTFHA.544@TK2MSFTNGP15.phx.gbl...
> Norman Yuan wrote:
>
> SQL Server 2005 will address this using schemas. For SQL 2000, a prefix
> is probably your best choice to do what you want.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

David Gugick

2005-04-28, 8:23 pm

Norman Yuan wrote:
> Glad to here that SQL 2005 does something on this, but sadly, my
> company may be stuck with SQL 2K for a quite while (we just get SQL2K
> up last year, don't see a reason to move to SQL2005 when it come out).
>
> Some of my SPs have 2 or 3, or even 4 prefixes. I might follow Keith's
> suggestion. Thanks for all replies.

I suppose another annoying option would be to use a separate databases
for each set of procedures. You would just need to fully qualify all
object access with the database name. I don't personally like the idea,
but it's there for the choosing...

--
David Gugick
Imceda Software
www.imceda.com

mark baekdal

2005-04-29, 7:23 am

If you use source control you can also organize your stored procedures under
different projects to help you and your developers. IE:

MyDatabase>Stored Procedures>Marketing

>Sales
>Billing
>etc

Software is available that can take everything from your source control,
compile it to find errors and deploy the changes - have a look at DB Ghost
http://www.dbghost.com

regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server

"Keith Kratochvil" wrote:

> I like the fact that they (Microsoft) do not make us follow any specific
> convention. This gives us (the DBAs, and developers) the opportunity to
> create our own "standardized" way of naming the stored procedures. Everyone
> probably has an opinion of how stored procedures should be named. A naming
> convention that I feel makes sense is
> usp_<action><table><description>
>
> usp stands for User Stored Procedure.
> Action could be SEL UPD DEL INS (continue the list as needed for your
> environment).
> Table refers to the table impacted. Of course this becomes more complicated
> when you have stored procedures that might reference more than one table,
> but this is where common sense comes in and you call it something that means
> something to you or your team).
> Description would be any additional information that would help describe the
> stored procedure to the person browsing the list of stored procedures.
>
> I have used this convention in systems that have thousands of stored
> procedures and it worked well for us.
>
> By the way, do you use source control to maintain your stored procedures?
> If not you might want to consider it. If you create and modify the stored
> procedures within Query Analyzer you have the option to save the file. Give
> the file a descriptive name (like the name of the stored procedure) and
> check it into your source code repository. When you need to make changes
> check out the file, make the change (using Query Analyzer), and execute the
> "create procedure" script to create (or re-create) the stored procedure in
> the appropriate database. You would then save the file and check it back
> into source control (with comments). This allows you to know what changed,
> when it changed, who changed it, and why. You could also build a system to
> automate the deployment of the stored procedures.
>
> --
> Keith
>
>
> "Norman Yuan" <NotReal@NotReal.not> wrote in message
> news:ewnuMRATFHA.3308@TK2MSFTNGP14.phx.gbl...
>
>
>

Richard Ding

2005-04-29, 8:23 pm

Any for the sake of simplicity, get rid of the underscores.

"Sgt. Sausage" <nobody@nowhere.com> wrote in message
news:2ce69$42713a67$
42a1e6c9$21021@FUSE.NET...
>
> "Richard Ding" <rding@acadian-asset.com> wrote in message
> news:%23AfaX3ATFHA.2336@TK2MSFTNGP12.phx.gbl...
>
> We use a multi-level prefix. e.g.
>
> usp_CS_ACCT_<whatever> means:
>
> user stored procedure, CodeSmith generated, for the
> Acctounting subsystem. Another example:
>
> usp_UI_SYS_Log<whatever> means:
>
> user stored procedure, for the UI component of the
> System logging function.
>
> I hate it. I'd rather be able to organize within EM
> using a folder metaphor as this is how we store the
> source in our source control system.
>
>

Sponsored Links