How to structure/organize for ease of maintenance?

Question

How to structure/organize for ease of maintenance?

Coriolan

SSCommitted

Points: 1931
More actions
October 12, 2005 at 8:15 am

#95436

I have 6 stored procs SP1-SP6. Each SP returns a result set RS1-RS6.
RS1-RS6 are used to populate the details of a Sql Server Reporting Services detailed report.
Now I have a summary report. The summary report only shows the counts. Its layout:
Project    ErrType1 ErrType2 ErrType3 ErrType4 ErrType5 ErrType6
----------------------------------------------------------------------
ProjectA      4            3           10            2            0             6
ProjectB       1           1            0             4            7             5
etc...
ErrType1 is the count of rows from SP1 result set1.
ErrType2 is the count of rows from SP2 result set2. And so on...
The 6 SPs currently take the ProjectID as input parameter.   How can I code and organize the SQL/script code to get the counts and so that if I have to change the SP SQL statements, I won't have to go to multiple places?  the SQL in each stored proc is very long ( join of 3-5 tables with derived table).
Note: because of the 0 count, I was hinted to use outer join. But I still struggle with the structure/organization to change each SP SQL only in one place instead of multiple places.
Thank you very much in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

While most of your explanation is thorough and well stated, I am a bit confused on your question. Do you want one SP, so you can change it if needs be, or keep 6 SP's in which case you may need to change all six? Or are you looking for one calling procedure for all six SP's?

I wasn't born stupid - I had to study.

Coriolan SSCommitted Points: 1931 More actions · Answer 2

I am looking for anything that is "simple and clean". My approach at this time is terrible.

Step 1: create COUNTSP1-COUNTSP6. What COUNTSP1 does is to

select count(*) from (exact same select in SP1) as derivedTable

Step 2: create a SUMMARYCOUNT stored proc that does the following:

create tempTable(ErrTyp1...ErrType6)

declare holders

open cursor

for each projectID

@holder1 = COUNTSP1 projectID as input parm

...

@holder6= COUNTSP6 projectID as input parm

insert(@holder1...@holder6) to tempTable

next projectID

Close cursor

select * from tempTable

return tempTable

------------------------------------------

tempTable is the result set for my summary report.

As you can see I end up having SP1-SP6, COUNTSP1-SP6,SUMMARYCOUNT=6+6+1 stored procs. The number of stored procs does not bother me but coding the same Select in SP1 and COUNTSP1 does.

The bottom line is how to best share data between SP1 and COUNTSP1?

I am also open to any approach. The existing detailed SP1-SP6 have their raison d'etre because they populate the detailed report.

How to build the result set for the summary report is the object.

Thank you

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

Let's look at this backwards. You have a known structure for a final output table, [Project, various ErrType counts]. Rather than use a CURSOR to run each of the six SP's for each individual Project, make a calling procedure which defines your #TempTable.

Drop COUNTSP1 - 6, and make SP1 - 6 have an output of the counts you need, (i.e., incorporate the SELECT COUNT(*) into the end of SP1 - 6 as its output).

So, now you have an empty table structure and a call to SP1 with output you insert into your #TempTable.

Next, call SP2 and, using IF EXISTS either UPDATE or INSERT into your #TempTable using the Project as the Primary Key.

That way, you do not need a cursor, you can go through all projects for each SP1 - 6 rather than one project at a time.

I am assuming that you may end up having a Project show up in SP2 that did not show up in SP1, hence my reasoning for the flow control of using IF EXISTS -- UPDATE, ELSE -- INSERT. This may not be the case, in which case it is even more simple, as every SP after SP1 will only require an update to you #TempTable.

(We use a function to populate a #Base table for most of our reports. This may also be an approach to look into as it will give you all of the Projects of interest right up front...)

I wasn't born stupid - I had to study.

Coriolan SSCommitted Points: 1931 More actions · Answer 4

I like your approach. It will probably help with performance.

I may have to ask more questions with implementing it but I think this implementation calls for my existing SP1-SP6 to return 2 result sets( one for the detailed report, one for the count summary table).

My count summary table will be built by making successive calls to SP1-SP6.

I think each call to SP1-SP6 will have 2 result sets. How do I instruct which result set to use (ie result set A for detail report, and result set B for count temptable after a call is made and both result sets are returned?)

Thanks.

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

Return your "Detail" resultset to a different predifnied table.

I believe you stated that your summary information is built from that. Build that at the end within your calling SP and roll down your summary information from that into its own table....

I wasn't born stupid - I had to study.

Coriolan SSCommitted Points: 1931 More actions · Answer 6

Coriolan

SSCommitted

Points: 1931

October 12, 2005 at 1:40 pm

#596927

It works like a charm. Thank you, Farrell.

Farrell Keough SSCoach Points: 17414 More actions · Answer 7

Glad I could help...

I wasn't born stupid - I had to study.