October 6, 2004 at 11:41 am
Can anyone recommend a white paper, book or course on how to write a technical specification for a SQL Stored procedure? I am trying to document some existing procedures (yes, I know that is backwards) but I don't have a good template to follow and I think what I have so far is junk.
Here's a query in the procedure:
UPDATE a WITH (TABLOCK)
SET psn_fac_prov_id = b.psn_facil_id,
fac_prov_id = ISNULL(b.psn_facil_id, a.PAYER_FAC_PROV_ID),
psn_fac_prov_name = c.psn_facility_name,
PAYER_FAC_PROV_NAME = ISNULL(b.PAYER_FACIL_NAME, a.PAYER_FAC_PROV_NAME),
fac_prov_name = isnull(c.psn_facility_name,
ISNULL(b.PAYER_FACIL_NAME,ISNULL(a.PAYER_FAC_PROV_NAME,'Unavailable'))),
fac_type = isnull(c.facility_care_type,'UNKNOWN'),
fac_state = c.FACILITY_STATE
FROM dbo.admissions_2004 a
LEFT OUTER JOIN DBO.FACILITY_PAYER b
ON payer_fac_prov_id = b.payer_facil_id
AND a.payer = b.payer
LEFT OUTER JOIN DBO.FACILITY_DICTIONARY c
ON b.psn_facil_id = c.psn_facility_id
And here's what I have written for the technical spec for this sql statement.
Update the facility columns in psn_admissions_2004 from the psn_facility_dictionary table and the psn_facility_payer table.
Set the psn_fac_prov_id equal to the psn_facil_id value from the facility payer table.
Set the fac_prov_id equal to the psn_facil_id from the facility payer table if psn_facil_id is not null. Otherwise set fac_prov_id equal to the payer_fac_prov_id column from the admissions table.
Set the psn_fac_prov_name equal to the psn_facility_name value from the facility dictionary.
Set the payer_fac_prov_name equal to the payer_facil_name value from the facility payer table if the payer_facil_name is not null. Otherwise set it to the payer_fac_prov_name value from the admissions table.
Set the fac_prov_name value equal one of the following choices:
Set fac_prov_name to the psn_facility_name value from the facility dictionary.
If psn_facility_name is NULL set to the payer_facil_name value from the facility_payer table.
If payer_facil_name is NULL set to payer_fac_prov_name from the admissions table.
If payer_fac_prov_name is NULL set to 'Unavailable'.
Set the fac_type value equal to the facility_care_type value from the facility dictionary.
If the facility_care_type value is NULL, set to 'UNKNOWN'
Set the fac_state value to the facility_state value from the facility dictionary
Join the psn_admissions_2004 table to the psn_facility_payer table where the payer_fac_provider id in the admissions table is equal to the payer_facil_id in the facility payer table and the payer value in the two tables are equal. Include all records in the admissions table and matching rows from the facility payer table.
Join the psn_facility_dictionary table to the psn_facility_payer table where the psn_facil_id from the facility payer table is equal to the psn_facility_id from the facility dictionary table. Include all records from the facility payer table and matching rows from the facility dictionary table.
I find it hard to read and hard to explain to others but I don't know how to fix it. Any suggestions?
Thanks in advance
October 6, 2004 at 3:38 pm
That's quite a description and no I don't get it either. I'm not sure there's an easier way, especially when there are left joins involved. I think you might do better with some sort of flow chart.
October 7, 2004 at 2:11 am
My two pennies worth is don't detail exactly what the stored procedure does, just create a general description. If someone needs to know the exact details they can look at the procedure and that will always be the most up to date document you have.
If you really need to detail it to a non-technical person then either use flow diagrams as suggested or use pseudocode, but don't foget that these documents need to be updated when the code gets updated and that can double the work.
October 7, 2004 at 12:46 pm
I agree with a graphical representation - A picture is worth few thousand words.
Also, try using bullets and points.
Maybe you can put it in a table of some kind.
October 7, 2004 at 1:34 pm
The detailed procedure can be gleaned from the code by anyone familiar with the database. Even for a technical spec, it's usually the "why" or the concept that are hard to comprehend.
Try to explain the objects and process in business (real-world) terms and explain why certain things are done.
For examples:
* Explain why "WITH (TABLOCK)" was specified
* Explain the join criteria and relationships between the tables, esp in business terms.
Of course, if there is a good data dictionary and the relationships are explained in business terms in some core documentation, then your documentation on particular stored proc should be much simpler.
October 8, 2004 at 2:00 am
There are two types os spec to consider :
Business specs should describe the functionaslity required and need not refer to SQL at all.
Systems specs on the other hand need top tell the developer and (more importantly) future maintainers, how it is supposed to work.
For the latter, I have tried various options but none that I have come up with has been satisfactory for complex queries. I agree that a graphical solution should be the right way to go but does anyone know of any standards, a Visio template or anything of the like?
October 8, 2004 at 7:07 am
Thanks for all of the great feedback. It sounds like I'm not alone in my search for standards.
I should have explained that although I am writing specs from code now, my goal is to be able to write the specification first and give it to a sql programmer to code. Also, I would like the spec to be a reference for maintenance of the code if the original programmer is not available to make changes. Stewart Joslyn has provided a good description of what I am looking for. Does anyone work in a department where business requirements have to be turned into a technical specification before coding begins? If so, could you post a small sample of one of those specs and then the code that was created from it? There must be a good way to explain where to get each value for in an update statement, what tables and columns to join, and what restrictions should be put in the "where" clause. That's what I'm looking for.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply