Pro SQL Server 2012 Reporting Services

CHAPTER 2  REPORT AUTHORING: DESIGNING EFFICIENT QUERIES

, E.EmployeeClassid

, E.EmployeeID

, DATENAME(mm, T.ChargeServiceStartDate)

, DATEPART(yy, T.ChargeServiceStartDate)

, S.ServiceTypeID

, T.ChargeServiceStartDate

ORDER BY

T.PatID

GO

Using ISNULL to Evaluate the Parameters

In the previous query, you added several new criteria to the WHERE clause for evaluating the parameters.

One of those criteria was the ISNULL function, used to evaluate the values of the database fields and

parameters.

(ISNULL(B.BranchID,0) = ISNULL(@BranchID,ISNULL(B.BranchID,0)))

AND

((CAST(DATEPART(yy, T.ChargeServiceStartDate) AS INT) = @ServiceYear

AND @ServiceYear IS NOT NULL)

OR @ServiceYear IS NULL)

AND

((CAST(DATEPART(mm, T.ChargeServiceStartDate) AS INT) = @ServiceMonth

AND @ServiceMonth IS NOT NULL)

OR @ServiceMonth IS NULL)

At first, the logic for these evaluations might seem a bit confusing, but remember that as long as the

criteria are equal, results are returned. This is true through the entire WHERE clause because it’s evaluated

with AND. This is easier to understand with the following sample statement:

SELECT * from Table1 WHERE 1 = 1

In this statement, all rows are returned, because 1 always equals 1. It doesn’t matter that you aren’t

comparing values from the table itself.

For the ISNULL function, you look to see whether the value of a database field—BranchID, for

example—contains a NULL value, and if so, ISNULL replaces NULL with zero. The right side of that equation

looks to see whether the @BranchID parameter was passed in as NULL; if so, then the value for @BranchID is

set to the value of BranchID in the database table and equals every row. If the @BranchID parameter is

passed to the stored procedure as a value—say, 2 for the branch Grid Iron—then only BranchID 2 is

returned because BranchID = @BranchID = 2. This evaluation is performed when there might be NULL

values in the field because NULL values can’t be compared with standard operators such as =.

For the two time values, Service Year and Month, you use similar logic. If the parameters

@ServiceMonth and @ServiceYear are passed in as NULL to the stored procedure, then the stored

procedure returns every record. If the parameters contain legitimate values, such as 2009 for the year,

the WHERE clause applies a filter when the parameter value equals the database value.

Query Performance and Indexes

While we’re talking about designing efficient queries, there are many things that DBAs can do for a table

to increase query performance. To list a few problems, statistics can get out of date, and indexes (where

there are any on a table) can become fragmented or insufficient. Each of these will affect query