[RESOLVED] Avoiding duplicate stored procedures

[Erwin69]

Hi,

The reporting area of an app I'm working on, needs to report results either by day, week, month or year. The resulting grid comes from a series of selects & joins, but has the same layout for each of the reports.

To be efficient, and avoid duplication, I thought to create a single stored procedure, and pass the full date part of the WHERE-clause as a single string parameter like in the code below:

SELECT Prijslijst.SoortIndex, Prijslijst.Omschrijving, Inkoop.X As 'Gewicht KG (Inkoop)', Inkoop.Y As 'Bedrag (Inkoop)', Verkoop.X As 'Gewicht KG (Verkoop)'

FROM 
	(SELECT Omschrijving, SUM(NettoGewicht) As X, SUM(TotaalPrijs) As Y
	FROM BonRegels
	WHERE LEFT(Bonnummer, 2) = 'IK' And @DateCriteria
	GROUP BY Omschrijving) Inkoop

FULL OUTER JOIN

	(SELECT Omschrijving, SUM(NettoGewicht) As X, SUM(TotaalPrijs) As Y
	FROM BonRegels
	WHERE LEFT(Bonnummer, 2) = 'VK' And @DateCriteria
	GROUP BY Omschrijving) Verkoop
	
ON Inkoop.Omschrijving = Verkoop.Omschrijving

LEFT JOIN Prijslijst
ON (Inkoop.Omschrijving = Prijslijst.Omschrijving) Or (Verkoop.Omschrijving = Prijslijst.Omschrijving)

ORDER BY SoortIndex

Now this is not accepted by SQL Server.

The question is:

1. Do I create 4 stored procedures each with a specific WHERE clause, running the risk having to update 4 times when a change in layout is requested?

or

2. Is there a smart way to create a single stored procedure?

or

3. Should I go for a single view without the WHERE-clause, and the create 4 different stored procedures for each of the day/week/month/year reports?

Thanks in advance for your help!

Regards,
Erwin

[jmcilhinney]

Write a sproc that accepts parameters for an arbitrary date range and then pass the appropriate parameter values for the range you want. You could even then four separate additional sprocs that do nothing but execute the first one but pass the appropriate calculated date values for the day, week, month or year ranges you want for your reports. That actually means one extra sproc but no duplication of code because the last four will be so simple.

[Erwin69]

Thanks for the feedback! I decided to go with a view that had the joins in it, and 4 stored procedures that simply do a SELECT * on the view with a specific WHERE clause for each of the options (day, weeknumber, etc.).