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:

Code:
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