CODEWITHSUNDEEP
sql-serverssms
Austin
Austin

Reputation: 75

How to use a parameter to select which query to use?

I am making a report and I need display accounts on this report that has done more than $5K in sales in the last 2 years or has done more than $500 YTD at the time the report is run.

DECLARE @Options INT = 1

--When I put 0 then
SELECT 
    Customer, SUM(Price) AS SALES
FROM 
    SOP30200 (NOLOCK)
WHERE 
(PostDate BETWEEN DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AND getdate())
GROUP BY Customer
HAVING SUM(Price) > 500
ORDER BY Customer

--When I put 1 then
SELECT 
    Customer, SUM(Price) AS SALES
FROM 
    SOP30200 (NOLOCK)
WHERE 
    PostDate BETWEEN DATEADD(Year,-2,GETDATE()) AND (GETDATE())
GROUP BY Customer
HAVING SUM(Price) > 5000
ORDER BY Customer

Upvotes: 0

Views: 37

Answers (3)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14097

One way would be to use CASE statements to do so:

SELECT 
    Customer, SUM(Price) AS SALES
FROM 
    SOP30200 (NOLOCK)
WHERE 
    PostDate BETWEEN CASE @Options
            WHEN 0 THEN DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
            WHEN 1 THEN DATEADD(Year,-2,GETDATE())
            ELSE NULL
        END
        AND (GETDATE())
GROUP BY Customer
HAVING SUM(Price) > CASE @Options
        WHEN 0 THEN 5000
        WHEN 1 THEN 500
        ELSE NULL
    END
ORDER BY Customer;

This doesn't really look pretty, but should be working (I might have left a syntax error).

Another way would be declaring variables and using them in your query:

DECLARE @PriceTreshold INT = CASE @Options
        WHEN 0 THEN 5000
        WHEN 1 THEN 500
        ELSE NULL
    END;
DECLARE @PostDateFrom DATETIME2 = CASE @Options
        WHEN 0 THEN DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
        WHEN 1 THEN DATEADD(Year,-2,GETDATE())
        ELSE NULL
    END;

SELECT 
    Customer, SUM(Price) AS SALES
FROM 
    SOP30200 (NOLOCK)
WHERE 
    PostDate BETWEEN @PostDateFrom AND (GETDATE())
GROUP BY Customer
HAVING SUM(Price) > @PriceTreshold
ORDER BY Customer;

Your queries aren't really different, they just have different filter criteria based on @Options.

If they were really that different, you could use this kind of syntax:

IF @Options = 0
BEGIN
    -- SQL query If @Options = 0
END
ELSE IF @Options = 1
BEGIN
    -- SQL query If @Options = 1
END

But that's not required in your case.

Upvotes: 1

TechGnome
TechGnome

Reputation: 315

In the simplest of terms, like this:

IF @Options = 1
  BEGIN
   ... query 1
  END
ELSE
  BEGIN
   ... query 2
  END
ELSE

Upvotes: 1

Rono
Rono

Reputation: 3371

A simple way to do that would be to add "AND @Options = 0" to the WHERE clause of the first query, add "AND @Options = 1" to the WHERE clause of the second one. Then combine the two queries into one by putting UNION between them.

Upvotes: 0

Related Questions