Creating A Query In A Stored Procedure

[Scott.Macmaster]

I need to create a stored procedure that returns details from one or more quarters in a fiscal year.

I suppose this would be simple if I could build a query in chunks based on the parameters passed into. If I'm wrong can somone show me how?

My solution involves 5 input parameters. One for the fiscal year and 4 indicating which quarters to inckude. I then declare 4 variables that default to 0. Depending on the values from the input parameters they are set to 1,2,3,4.

ALTER PROCEDURE [dbo].[Report_DetailsByQuarter]
	@FiscalYear		int,
	@UseQuarter1	bit,
	@UseQuarter2	bit,
	@UseQuarter3	bit,
	@UseQuarter4	bit
AS
BEGIN
	DECLARE @Quarter1 INT
	DECLARE @Quarter2 INT
	DECLARE @Quarter3 INT
	DECLARE @Quarter4 INT
	
	SET @Quarter1 = 0
	SET @Quarter2 = 0
	SET @Quarter3 = 0
	SET @Quarter4 = 0
	
	IF @UseQuarter1 = 1 BEGIN
		SET @Quarter1 = 1
	END
	IF @UseQuarter2 = 1 BEGIN
		SET @Quarter2 = 2
	END
	IF @UseQuarter3 = 1 BEGIN
		SET @Quarter3 = 3
	END
	IF @UseQuarter4 = 1 BEGIN
		SET @Quarter4 = 4
	END

	SELECT *
	FROM    Sales
	WHERE Sales.FiscalYear = @FiscalYear
		AND Sales.Quarter IN (@Quarter1,@Quarter2,@Quarter3,@Quarter4)

END

This seems slightly inefficient to me since it has to check Sales.Quarter against 4 values no matter how many quarters are requested.

Does someone know if there's a better approach? If so, what is it?


Thanks,