September 1, 2009 at 9:50 am
In my work environment we use stored procedure as a dataset for reporting services. Currently we have close to 100 stored procedures that have been created for reports. Of those about 25-30 remain the "main" stored procedures while the other one's are more specific stored procedures. The main stored procedures are the one's that are used more often and in a lot of reports. Now there are also new stored procedures that are being branched stored procedure as child procedures of the "main" procedures. We are looking for ways to organize the stored procedures so it doesn't get clumsy over time. We were suggested using assemblies like in programming language to organize stored procedures. However it seemed a very vague concept to adopt. Can anyone throw some light upon better way to organize the stored procedures.
Any advice would be appreciated.
Thanks in advance.
September 7, 2009 at 3:45 pm
Naming convention. Documentation.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 7, 2009 at 5:12 pm
You may want to examine the grouping of your stored procedures using this group of create statements.
CREATE PROCEDURE my_Proc_Name;1
AS
SELECT some column from some table
GO
CREATE PROCEDURE my_Proc_Name;2
AS
SELECT some other column FROM some table[/]
GO
CREATE PROCEDURE my_Proc_Name;3
AS
SELECT some series of columns from some table
GO
The advantage to this is that the only SP to appear in SSMS will be named my_Proc_Name, and when scripted to be modified ALL the sp's will be in that one SSMS window. Thus a change to one which would require a change to another SP will be fairly easy to follow. In your case as stated you would have only 25-30 main (Individually named) SPs each of which will contain the pertinent branch or child SPs.
The disadvantage to this technique is that to drop a query ALL the queries will be dropped. That is you will issue the command DROP PROC my_Proc_Name and in this example all 3 SPs will be dropped.
Further recommended reading
1. ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/afe3d86d-c9ab-44e4-b74d-4e3dbd9cc58c.htm and
2. http://technet.microsoft.com/en-us/library/aa214379(SQL.80).aspx
From ref 1.
; number
Is an optional integer that is used to group procedures of the same name. These grouped procedures can be dropped together by using one DROP PROCEDURE statement. For example, an application called orders might use procedures named orderproc;1, orderproc;2, and so on. The DROP PROCEDURE orderproc statement drops the whole group. If the name contains delimited identifiers, the number should not be included as part of the identifier; use the appropriate delimiter around only procedure_name.
This posting is just to point out a mostly forgotten aspect of naming stored procedures. Whatever path you choose, document, document, document.
September 8, 2009 at 11:18 am
Bitbucket has a good method. The other method that I would suggest is to use schema's as well as naming convention. It could be as simple as 1 Reports schema or ReportPerson, ReportCustomer. As detailed as you choose.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 8, 2009 at 1:19 pm
like other people are saying ... documentation and naming convention all you need 😉
i.e. naming procs similar to reports and so on...
September 8, 2009 at 4:38 pm
bitbucket (9/7/2009)
You may want to examine the grouping of your stored procedures using this group of create statements.CREATE PROCEDURE my_Proc_Name;1
AS
SELECT some column from some table
GO
CREATE PROCEDURE my_Proc_Name;2
AS
SELECT some other column FROM some table[/]
GO
CREATE PROCEDURE my_Proc_Name;3
AS
SELECT some series of columns from some table
GO
wow!... you are pretty close of discovering what we call "packages" in the Oracle world.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 8, 2009 at 6:50 pm
PaulB (9/8/2009)
wow!... you are pretty close of discovering what we call "packages" in the Oracle world.
Please let's not get all my-RDBMS-is-better-than-your-RDBMS 🙂
It's kinda boring eh.
Just to add:
The ability to number stored procedures is deprecated, so I wouldn't recommend using it.
All other suggestions super-good.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply