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,

[marceln]

One way would be to use dynamic SQL only for the SELECT clause and test only the valid quarter values.

[Scott.Macmaster]

Well, that won't work for me. I'm moving this query from code to a stored procedure to avoid the overhead of sql server having to figure out a query plan each time I run the query. Changing the query into a dynamic query will defeat the purpose of doing this.

The real query isn't a simple 3 line thing. It's about 50 lines and joins 7 tables together.

Any other suggestions.


Thanks,

[marceln]

Then how about this:

DECLARE @Quarters AS int
SET @Quarters = @Quarter1 | @Quarter2 | @Quarter3 | @Quarter4

SELECT *
    FROM    Sales
    WHERE Sales.FiscalYear = @FiscalYear
        AND (Sales.Quarter & @Quarters) != 0

A bitwise operation is certainly faster that at most 4 comparisons.

[Scott.Macmaster]

It would be faster. However, this won't work since I'm querying a third party database I can't change the codes for quarters. The codes are 1,2,3,4. If they were 1,2,4,8. It would.


Thanks,

[TheCPUWizard]

It would be faster. However, this won't work since I'm querying a third party database I can't change the codes for quarters. The codes are 1,2,3,4. If they were 1,2,4,8. It would.


Thanks,

But you CAN create a UDF that does the translation, but I dont know about performance on that one.

You will probably (I have not measured) get the best by using 4 compares rather than an "In", the optimizer can do some interesting things....

typed in raw...syntax errors probalbe.

Where
(Sales.Quarter = @Quarter1) OR IsNull(@Quarter1)))
AND
(Sales.Quarter = @Quarter2) OR IsNull(@Quarter2)))
AND
(Sales.Quarter = @Quarter3) OR IsNull(@Quarter3)))
AMD
(Sales.Quarter = @Quarter4) OR IsNull(@Quarter4)))

It is worth giving it a try.....

[Scott.Macmaster]

Yea, I would avoid a UDF. That would probably slow things down.

As for your solution, it doesn't look right. If I didn't use IN. I think it should be

Where
  Sales.FiscalYear = @FiscalYear
  AND (Sales.Quarter = @Quarter1
    OR Sales.Quarter = @Quarter2
    OR Sales.Quarter = @Quarter3
    OR Sales.Quarter = @Quarter4)

I probably could let @Quarter1-4 default to NULL and not bother setting setting them to 0. However, someone sometime ago told me said or's are inefficient. I suppose IN implies or's. So there's no real difference. I don't see how how the optimizer could do better this way then with using IN.

I can't help thinking trying to optimize this aspect of the query is trivial. I just read that I shoould have dbo. in front of all my tables in the from clause.

Well, now that I have several options I should run some tests to see what is best.


Thanks,

[TheCPUWizard]

No, you do not want to use the OR. You want to use the AND along with the sub-OR of checking for null.

This provides an opportunity for optimization since a certain set (often 3) of the parameters WILL be null, and therefor will become a constant true, eliminating the 3 clauses from calculation!

[Scott.Macmaster]

IsNull() wants 2 parameters. Also, your parenthesis don't made sense should it be this?

WHERE Sales.FiscalYear = @FiscalYear
   AND (Sales.Quarter = @Quarter1 OR @Quarter1 IS NULL)
   AND (Sales.Quarter = @Quarter2 OR @Quarter2 IS NULL)
   AND (Sales.Quarter = @Quarter3 OR @Quarter3 IS NULL)
   AND (Sales.Quarter = @Quarter4 OR @Quarter4 IS NULL)

Thanks,

[Krzemo]

Some concepts...

First IsNull() OR "(Sales.Quarter = @QuarterXX OR @QuarterXX IS NULL)" (assumption is made that variables are set to null when "not set", not to "0" as in first, original example)

WHERE Sales.FiscalYear = @FiscalYear
   AND Sales.Quarter = IsNull(@Quarter1,Sales.Quarter)
   AND Sales.Quarter =  IsNull(@Quarter2,Sales.Quarter)
   AND Sales.Quarter =  IsNull(@Quarter3,Sales.Quarter)
   AND Sales.Quarter =  IsNull(@Quarter4,Sales.Quarter)

It is usually good but not this time (I suppose). It will allways be "false" in situations when at list 2 variables are set (because Sales.Quarter cannot be equal to, for example, to "1" and "2" at the same time).
;-)

So ... UNION concept:

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

But it looks the same (to me) as "IN" (but maybe worth trying)

So maybe different approach:

DECLARE @quarters TABLE (quarter INT NOT NULL PRIMARY KEY) 
IF @UseQuarter1=1 INSERT INTO @quarters(quarter) VALUES(1)
IF @UseQuarter2=1 INSERT INTO @quarters(quarter) VALUES(2)
IF @UseQuarter3=1 INSERT INTO @quarters(quarter) VALUES(3)
IF @UseQuarter4=1 INSERT INTO @quarters(quarter) VALUES(4)
 
SELECT *
FROM    Sales s
INNER JOIN @quarters q ON q.quarter=s.Quarter
WHERE s.FiscalYear = @FiscalYear

It should work (worth trying).
;-)

And bitwise concept:

AND (POWER(2,Sales.Quarter-1) & @Quarters) != 0

Hope it helps
;-)

Best regards,
Krzemo.