Creating A Query In A Stored Procedure

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.

Code:

---

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,

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

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,

Then how about this:

Code:

---

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.

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,

Quote:

---

Originally Posted by 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,

---

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.

Code:

---

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.....

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

Code:

---

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,

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!

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

Code:

---

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,

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)

Code:

---

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:

Code:

---

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:

Code:

---

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:

Code:

---

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

---

Hope it helps
;-)

Best regards,
Krzemo.