Jiwa Stored Procedures

Discussion of features and enhancements to the Jiwa product.

Jiwa Stored Procedures

Postby jiwameister » Tue Mar 11, 2008 11:59 pm

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
Glenn Lake
Managing Director
Lonicera Pty Ltd
http://www.lonicera.com.au
User avatar
jiwameister
Occasional Contributor
Occasional Contributor
 
Posts: 27
Joined: Sun Mar 02, 2008 12:36 am
Location: Melbourne, Australia

Re: Jiwa Stored Procedures

Postby Scott.Pearce » Wed Mar 12, 2008 12:42 pm

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?
Scott Pearce
Senior Analyst/Programmer
Jiwa Financials
User avatar
Scott.Pearce
Senpai
Senpai
 
Posts: 742
Joined: Tue Feb 12, 2008 11:27 am
Location: New South Wales, Australia
Topics Solved: 221

Re: Jiwa Stored Procedures

Postby Mike.Sheen » Wed Nov 19, 2008 7:44 pm

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.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Jiwa Stored Procedures

Postby pricerc » Wed Aug 31, 2011 8:28 am

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.
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20


Return to Features and Enhancements

Who is online

Users browsing this forum: No registered users and 1 guest

cron