October 6, 2008 at 4:05 am
When writing a stored procedure it is usually named usp_ but I was told that SP's for reports are named rp_ Can anyone confirm or recommend another naming convention to differentiate reporting SPs from other SPs?
-Hannah
October 6, 2008 at 4:46 am
I think you can go ahead with what you suggested as long as all the procedures follow the same convention. We used to name report stored procedures with "RPT" (RPT_CustomerList) etc. After migrating to SQL Server 2005, we started using schemas and it become "Report.CustomerList".
I think any naming convention is equally good. The most important factor is that all the objects should follow the convention.
.
October 7, 2008 at 10:12 am
I agree completely. Prior to SQL 2005 and schemas, I switched to a convention that I think I invented which I called Hungarian post notation. Its purpose was to circumvent the signal-to-noise ratio involved in naming schemes that involve prefixes (i.e. all sprocs begin with "ap_". This creates a huge amount of noise. You have to ignore the first 3 chars at minimum. Given a sproc called "rpt_Customer" it is IMO better named "Customer_rpt" because then when you look at the objects in Object Explorer, all the Customer sprocs are there at once.
The worst naming convention of all, in my opinion, places the target after the verb after the prefix, such as:
ap_GetCustomerInfo
This results in incredible signal-to-noise ratio problems. If you want to see the list of sprocs pertaining to Customer, you have to visit ap_Get*, ap_Insert*, ap_Delete* and ap_Update*. Insane, IMO.
The object of interest ought to be the first part of the name, followed by the intention. So we would end up with something like this ("ap" means application procedure; first and most fundamental no-no in naming schemes is to use "sp" as a prefix -- leave that one to MS):
Customer_Delete_ap
Customer_Insert_ap
Customer_Select_ap
Customer_Update_ap
Assuming a naming scheme like this, if you open Object Explorer and drill down to Stored Procedures, everything will be sorted by target table(s), and always appear in a predictable order. Signal-to-noise ratio has just been eliminated.
hth,
Arthur
Arthur Fuller
cell: 647-710-1314
Only two businesses refer to their clients as users: drug-dealing and software development.
-- Arthur Fuller
October 7, 2008 at 10:28 am
Well, i think that in the case of reports it makes sense to add rpt_ as generally anyone who is looking for a report would prefer to see all of the reports grouped together. Also, in many companies the reports are not managed by the DB department so certain people will only be looking at report stored procedures and won't be interested in the other SP's, while the DBAs aren't interested in the reports. But I agree that there is not much point in adding usp_ to all of the other SPs, that does just make it harder to read. There is definitely a fine balance in these prefixes between making things more organized and just making them harder to find.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply