June 23, 2006 at 8:13 am
I currently have a somewhat large web application. For some of the code I use Inline SQL code, not Stored Procedures. I know stored procedures are the better way to go, but the problem is that in SQL 2000 (which is what I have) each database only has one location to store my stored procedures, therefore I would have hundreds per database which can become very messy to deal with as a developer. Is there no better way to organize stored procedures in SQL 2000? I would like to organize them in some sort of file structure. Where I could create folders for different procedure types inside the database. Does anyone have any suggestions?
Thanks,
Adam
June 23, 2006 at 10:41 am
What you describe can be done, but (and it's a little late I guess) structured naming solves a lot of the problem up front, grouping procedures by logical area, using a SystemSubsystemUse scheme. Procedures that are logically related will appear next to each other by just doing a simple sort.
The key is to put the verb *last*, otherwise you have 100 "Insert..." procedures followed by 100 "Select..." procedures
dbo.CustomerOrderBilledItemList
dbo.CustomerOrderDetails
dbo.CustomerOrderPrepare
dbo.CustomerContactDelete
dbo.CustomerContactInsert
dbo.CustomerContactList
dbo.CustomerContactUpdate
...
dbo.OrdersMarkComplete
dbo.OrdersProductionStageList
dbo.OrdersStatusReport
...
dbo.ShippingPackingListPrint
dbo.ShippingReturnedItemProcess
...
dbo.ReportsInventoryDailyUse
dbo.ReportsInventoryForecast
But for your request, you can manage all of your objects in a file/folder structure using a technique many of us are using: treat everything as a file. (Note: the following advice is culled from some articles elsewhere on this site about source code control and bits in some Ken Henderson "Guru's Guide" books.)
Start by scripting each individual object to a separate file, named for the object (dbo.MyProc would go to a file called "dbo.MyProc.prc"). Enterprise Manager provides a feature to script everything out like this in one shot (I forget the name, I've only got 2005 installed locally). *** Important: while setting that up, look through the dialogs for a setting of the output type. It will default to Unicode. Set that to ANSI text so the text files can be compared later. *** Also, be sure to check the "Check for object existance and drop if it exists" (I forget the exact text of that, too) option so every file can built as a CREATE PROC/FUNCTION/TABLE/etc. You also want to include a USE [databasename] statement at the top of each file.
Once you've got all those files dumped into one folder, you can go to work building your folder structure as you see fit, moving the files to where you would like them stored.
When you have that fully organized, check the entire tree into SourceSafe or your source code repository of choice. Big tip: set the working folder of *all* the folders in the project to the same easy-to-reach folder, like C:\sql. That way, when you check files out, you can quickly navigate to the file.
Next: stop using Enterprise Manager for development, and shoot anyone who tries.
To modify any procedure/function/table definition, just check the file out and drag it into Query Analyzer, and edit/execute/test everything from there. I usually include code for testing my objects in a block comment at the bottom of the file, so I can easily do basic bench testing by just highlighting the test code and executing it.
/*
-- SAMPLE EXECUTE
DECLARE @res varchar(50)
EXEC dbo.MyNeatProc 'SampleValue', @res OUTPUT
SELECT @res
*/
When you're done working with it, save it and check it back in. If you originally built the file as ANSI text, you can view the differences on the different version, too.
To create new procedures, etc., you can copy an existing file and do a little replacement, and all the USE [databasename], SET options, and IF EXISTS... blocks are pre-written. Save the file in a folder of your choosing, and check it in.
To keep that local working folder manageable, I always check the "Delete local copy" box when I check files in. The folder only contains things I have checked out, instead of the thousands of procedures and whatnot that exist in the database.
For the advanced version, create separate project trees in your source code repository for dev, test, and production. That way you can view the state of any object in each system, and run full-project difference comparisons of all objects - allows an easy way to see what's in test that isn't in production, and so on.
-Eddie
Eddie Wuerch
MCM: SQL
April 22, 2010 at 8:15 am
I completely agree!
Wouldn't it be GREAT if you could have a folder structure within SQL Server Management Studio such as:
+ Programmability
+ Stored Procedures
+ System Stored Procedures
+ Export Procedures
+ Import Procedures
+ Report Procedures
+ Table Refresh Procedures
(etc.)
Right now, the only solution is to prefix and bundle your procedures by names:
Export_Customers_ForEDI
Export_Orders_ForEDI
Export_POs_ForEDI
Import_Customers_FromEDI
Import_Customers_FromWeb
Import_Orders_FromEDI
Import_POs_FromCustomPOS
Import_POs_FromEDI
Report_CustomerList
Report_OrderList_ByOffice
Report_OrderList_ByRegion
Report_OrderList_Overdue
Report_POs_DueToday
Report_POs_Returns
TableRefresh_CustomerOrders
TableRefresh_PODetails
(etc.)
Some programmers will use single prefixes instead, or Hungarian notation.
rCustomerList
rOrderList_ByRegion
zRefresh_CustomerOrders (z used to move it to the end of the list)
zRefresh_PODetails
or
refCustomerOrders
refPODetails
rptCustomerList
rptOrderList_ByRegion
However, as Joe Celko advises (and I agree), that's poor naming convention style.
Really, though, while the sub-folders would make things nice and neat and tidy for those of us who obsess about such organization (and those of you who LOVE to use pTouch labellers know who you are), good naming convention would still require some kind of prefix to identify the procedure anyway.
If you put all the report procedures into their own folder called [Report Procedures], you'd still want to identify them by name with some indicator that it was a report, vs. a list query or an update query.
I suppose it gives you the option of prefixing the report with a table or topic name, versus the functionality.
Most developers prefix their SP names with table names, then specify the action or purpose:
Orders_Del
Orders_InsUpd
Orders_ListByCustomer
Orders_ListByDate
But I've seen some start with the action or purpose first, then break it out (although this always seems awkward and even less organized to me):
InsUpd_Customers
InsUpd_Orders
InsUpd_POs
List_OfficeLocations
List_OrdersByCustomers
List_OrdersByDate
Prefixing the category onto the SP name is really a hybrid of both methods.
Import_Orders_FromEDI
Import_POs_FromCustomPOS
Import_POs_FromEDI
Report_CustomerList
Report_OrderList_ByOffice
Report_OrderList_ByRegion
TableRefresh_CustomerOrders
And if you DID have separate folders for each, you'd still want to keep those prefixes or somehow indicate the actual purpose of the SP. You definitely would NOT want to name them like this:
(within the Import subfolder)
Orders_FromEDI
POs_FromCustomPOS
POs_FromEDI
(within the Reports subfolder)
CustomerList
OrderList_ByOffice
OrderList_ByRegion
(within the TableRefresh subfolder)
CustomerOrders
Because then, actual reference to those SP's in code would make it very confusing where you'd actually find them in the folder sub-structure. (How would you know CustomerOrders refers to a normalized table refresh routine, vs. a report or a list?)
So the bottom line is, folders sound like something we'd want. But essentially it ends up buying us very little. I'd still love to see it -- I label everything, even light switches 😛 -- but my SP's within each folder would probably be prefixed with the folder name anyway.
It would certainly be cool if MS embraced the XML concept and it automatically created folders for similarly prefixed procedures, tables, etc. (Hint hint, wink wink, nudge nudge, knowhatimean MS?)
[font="Verdana"]If technology is supposed to give us more freedom and empower us to pursue the more important things in life, why do so many people allow themselves to become enslaved by it? Always remember, the truly important people cannot be reached... except when they want to reach you.[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply