There are many different ways to do this. Each way has an advantage and a disadvantage, so I will attempt to explain this as best I can...
One query method
Looking at the 2 queries involved, I notice that the only difference is in the where clause.
[tt]
where [YEAR] = @Year
where [YEAR] = @Year and [Quarter] = @Quarter and [MONTH] = @Month[/tt]
It looks like you want to use the simplified where clause if @Quarter is NULL and @Month is NULL. There is a way to make a where clause that accommodates this condition. Like this:
Code:
SELECT CASE Targeted_Account
WHEN 'TRUE' THEN 'Targeted' ELSE 'Un-Targeted' END AS [Customer Type],
COUNT(DISTINCT MaginusAccount) AS [Resellers]
FROM dbo.V_TimeCard
where [YEAR] = @Year
[!]and (@Quarter Is Null Or [Quarter] = @Quarter)
and (@Month Is NULL or [MONTH] = @Month)[/!]
GROUP BY Targeted_Account
This one query does the same thing as your 2 separate queries. Specifically... the results would always be the same. The benefit of this approach is that you only have one query to maintain. The disadvantage of this method is that the query may be ever so slightly slower because OR conditions don't perform as well.
Multiple Query Method
Instead of using GOTO's in your code, you could reorganize it a little so that the goto's are gone, but you effectively get the same execution. Like this:
Code:
Declare @Year varchar(4) = '2010', @Quarter varchar(9) = null, @Month varchar(3) = Null
If @Quarter = null and @Month = null
Begin
SELECT 'StepOne', CASE Targeted_Account
WHEN 'TRUE' THEN 'Targeted' ELSE 'Un-Targeted' END AS [Customer Type],
COUNT(DISTINCT MaginusAccount) AS [Resellers]
FROM dbo.V_TimeCard
where [YEAR] = @Year
GROUP BY Targeted_Account
End
Else
Begin
SELECT 'StepOne', CASE Targeted_Account
WHEN 'TRUE' THEN 'Targeted' ELSE 'Un-Targeted' END AS [Customer Type],
COUNT(DISTINCT MaginusAccount) AS [Resellers]
FROM dbo.V_TimeCard
where [YEAR] = @Year and [Quarter] = @Quarter and [MONTH] = @Month
GROUP BY Targeted_Account
End
The disadvantage of this query is that there are actually multiple queries here. If you wanted to modify this code to add another column, you would need to modify both queries. To make things worse, suppose you wanted to handle 3 "possibly null" parameters in any combination. You would need to write 8 different queries. This makes maintenance harder. The advantage to this method is that there are no OR's and each query will execute faster than the first method I showed.
Dynamic SQL
You can use dynamic sql to run the query. With dynamic sql, you are essentially building a string that represents your query and then you execute it.
Ex:
Code:
Declare @Year varchar(4) = '2010', @Quarter varchar(9) = null, @Month varchar(3) = Null
Declare @SQL VarChar(8000)
Set @SQL = 'SELECT ''StepOne'', CASE Targeted_Account
WHEN ''TRUE'' THEN ''Targeted'' ELSE ''Un-Targeted'' END AS [Customer Type],
COUNT(DISTINCT MaginusAccount) AS [Resellers]
FROM dbo.V_TimeCard
where 1 = 1'
If @Year Is Not NULL
Set @SQL = @SQL + ' and [Year] = ''' + @Year + ''''
If @Quarter Is Not NULL
Set @SQL = @SQL + ' and [Quarter] = ''' + @Quarter + ''''
If @Month Is Not NULL
Set @SQL = @SQL + ' and [Month] = ''' + @Month + ''''
Set @SQL = @SQL + ' GROUP BY Targeted_Account'
Exec (@SQL)
The advantage with this approach is that there is only one query to maintain, and it executes faster. The disadvantage is that you need to be careful about SQL injection, and EXEC is disabled on the server by default when using SQL2005 or newer. The query I show above is susceptible to SQL Injection, but there are thing you can do to help protect yourself, too. If you decide to use dynamic sql, make sure you read up on [google]Dynamic SQL[/google] and [google]SQL Injection[/google].
-
George
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
