Jiwa

Hi,

We came across a problem today with the Stored Procedures for reports.. Was nothing major - but the thing that came to light was there is a large growing list of stored procedures, and its not easy to see the difference between them - sometimes there is only one letter (ie a plural S) between different Stored Procs.

My suggestion is to create a naming convention that makes it obvious which SP's are used for reporting, which are used for internal Jiwa use - and which are actually sub-stored procedures - ie called by other Stored Procs and not used in their own right.

Thanks

and its not easy to see the difference between them - sometimes there is only one letter (ie a plural S) between different Stored Procs.

Agreed, but the pluralised ones are quite valid. For example, there is a stored procedure that gets an opening balance for a single debtor, and another that gets an opening balance for all debtors. Therefore, they should have the same name (except for the extra "s") because they do the same thing (almost). One just needs to be careful.

My suggestion is to create a naming convention that makes it obvious which SP's are used for reporting, which are used for internal Jiwa use - and which are actually sub-stored procedures - ie called by other Stored Procs and not used in their own right.

We do have a naming convention:

usp_COMPANYNAME_SECTION_DESCRIPTIVENAME

and we try to stick to it, and for the most part we have. However this convention does not differentiate between different "types" of stored procedure. The reason for this is that many of the stored procs are used for reporting *and* internal use *and* as sub-stored procedures. How should they be named?

Perhaps some additional comments in the header section of the stored procedure to explain the stored procedures purpose would avoid the confusion.

As far as the naming is concerned, I've not yet seen an argument which would sway me to change the naming convention... but I fully understand the problem being presented. Hopefully a comment atop the stored proc will address this.

With only SQL 2005 or later supported these days, an option may be to use schemas to assist with separating the various functions.

I've been doing this for a few clients now, where I'm adding tables and/or stored procedures to existing systems (including JIWA) - rather than prefixing the name of the stored procedure, I use a different schema, so I'd have report.MonthlyPerformance rather than dbo.report_MonthlyPerformance. And 'tracking' tables added for auditing purposes all live in a 'tracking' schema.

In 'another life', I used schemas more extensively, with a different schema for each 'module' in the database I was working on. I think Microsoft have used the same technique in some example database. So you could use a schema for each module: debtors, creditors, gl, instead of prefixes on each database object. E.g. debtors.Main instead of DB_Main; creditors.Main instead of CR_Main. gl.Config instead of GL_Config, etc.

Local, non-Jiwa additions could use their own schema, perhaps a special one reserved by Jiwa in the first place.

It takes a *little* getting used to, but I found that once I'd started, I wondered why I hadn't started earlier.