Nse Nse - 2 months ago 8
SQL Question

SQL LEFT JOIN Query reporting with optional parameters

I'm running a query that generates a report for an inventory system. The query takes in 6 parameters, 3 of which are optional. The query works fine if all the parameters are entered, however I can't get the query to operate correctly when one or more optional parameters are omitted.

The important parameters are

@AccountNumber
,
@Branch
,
@Department
, and
@OrderBy
. The
@AccountNumber
parameter is required while the other three are optional. If
@Branch
is omitted I would like the report to show all of the inventory in that account. Similarly, If
@Department
is omitted but
@Branch
is present I would like to show all of the inventory in that account's branch. If all three parameters are present then it will show all of the inventory in that account's branch and department. If
@OrderBy
is omitted then the report orders the inventory by account number ordered ascending by default. The query that I'm using is below:

USE database;
GO
CREATE PROCEDURE RetrievedList
@AccountNumber int,
@Branch nvarchar(50),
@Department nvarchar(50),
@StartDate date,
@EndDate date,
@OrderBy nvarchar(10)
AS

IF @OrderBy = 'Locator'
BEGIN
SELECT [Container].[Acct] AS [Account]
...
FROM [File] LEFT JOIN [Container]
ON [File].[BoxID] = [Container].[BoxID]
WHERE [Container].[Acct] = @AccountNumber
AND [Container].[Branch] = @Branch
AND [Container].[Dept] = @Department
AND [File].[Out_Date] IS NOT NULL
AND [File].[Out_Date] BETWEEN @StartDate AND @EndDate
ORDER BY [Container].[Loc];
END
ELSE IF @OrderBy = 'Title'
BEGIN
SELECT [Container].[Acct] AS [Account]
...
FROM [File] LEFT JOIN [Container]
ON [File].[BoxID] = [Container].[BoxID]
WHERE [Container].[Acct] = @AccountNumber
AND [Container].[Branch] = @Branch
AND [Container].[Dept] = @Department
AND [File].[Out_Date] IS NOT NULL
AND [File].[Out_Date] BETWEEN @StartDate AND @EndDate
ORDER BY [File].[Title1];
END
ELSE
BEGIN
SELECT [Container].[Acct] AS [Account]
...
FROM [File] LEFT JOIN [Container]
ON [File].[BoxID] = [Container].[BoxID]
WHERE [Container].[Acct] = @AccountNumber
AND [Container].[Branch] = @Branch
AND [Container].[Dept] = @Department
AND [File].[Out_Date] IS NOT NULL
AND [File].[Out_Date] BETWEEN @StartDate AND @EndDate
ORDER BY [Container].[Acct], [Container].[Branch], [Container].[Dept], [Container].[BoxNo], [File].[Title1];
END
GO


Keep in mind that I can't use the
WHERE [Container].[Branch] = @Branch OR [Container].[Branch] = NULL
approach because of the
LEFT JOIN
; it returns six times the records that are expected.

Answer

My typical pattern for this issue is using the IsNull operator like this:

AND [Container].[Branch] = IsNull(@Branch, [Container].[Branch])

It will compare against the parameter if passed; otherwise, it'll compare the column to itself.

Comments