CODEWITHSUNDEEP
sql-server
Sree
Sree

Reputation: 75

How to Return All records if Filter is NULL (Empty)

I have a report which shows purchase indents over a period of time created by indentor. Here, @Name parameter is used to filter indentor. But, the problem is, if @Name parameter is blank then the report is returning null records. I want to show the report with all Indentors if nothing is selected in @Name filter. Below is the Query. I'm new to SQL.

SELECT INH.No_, INH.[Approved Date],  
INH.Indentor, INL.No_ AS ItemCode, 
INL.Description, INL.Description2, 
INL.Req_Quantity, INL.[Unit of Measure], 
PL.[Document No_], PH.[Order Date], PL.Quantity AS OrderedQuantity, PL.[Quantity Received]
FROM [Company$Indent Header] AS INH
INNER JOIN
[Company$Indent Line] AS INL
ON INH.No_ = INL.[Document No_]
INNER JOIN
[Company$Purchase Line] AS PL
ON INL.[Document No_] = PL.[Indent No_] AND INL.[Line No_] = PL.[Indent Line No_]
INNER JOIN
[Company$Purchase Header] AS PH 
ON PL.[Document No_] = PH.No_
WHERE (INH.Indentor = @Name) AND (INL.No_ <> '') AND 
(INH.[Approved Date] BETWEEN @StartDate AND @EndDate)
ORDER BY ItemCode

Upvotes: 1

Views: 1878

Answers (3)

Devart
Devart

Reputation: 121922

Try this one -

SELECT
      INH.No_
    , INH.[Approved Date]
    , INH.Indentor
    , ItemCode = INL.No_ 
    , INL.[description]
    , INL.Description2
    , INL.Req_Quantity
    , INL.[Unit of Measure]
    , PL.[Document No_]
    , PH.[Order Date]
    , OrderedQuantity = PL.Quantity 
    , PL.[Quantity Received]
FROM dbo.[Company$Indent Header] INH
JOIN dbo.[Company$Indent Line] INL ON INH.No_ = INL.[Document No_]
JOIN dbo.[Company$Purchase Line] PL ON INL.[Document No_] = PL.[Indent No_] AND INL.[Line No_] = PL.[Indent Line No_]
JOIN dbo.[Company$Purchase Header] PH ON PL.[Document No_] = PH.No_
WHERE INH.Indentor = ISNULL(@Name, INH.Indentor)
    AND INL.No_ != ''
    AND INH.[Approved Date] BETWEEN @StartDate AND @EndDate
ORDER BY ItemCode

Upvotes: 0

Md. Parvez Alam
Md. Parvez Alam

Reputation: 4596

use some thing like this

WHERE (INH.Indentor = COALESCE(@Name,INH.Indentor)

and make sure at the beginning of stored procedure after parameters check

if len(@Name) = 0
set @Name = null

so that if @Name is blank it will put null then COALESCE(@Name,INH.Indentor) will check if @Name is null, then check with existing value

Upvotes: 1

Mike de Klerk
Mike de Klerk

Reputation: 12328

How is your query build. By code? Because then I would simply leave out (INH.Indentor = @Name) from the following piece WHERE (INH.Indentor = @Name) AND if @Name would be empty. I guess there is no condition in SQL whether you want to take into account a condition in WHERE clausule or not, correct me if I am wrong.

Upvotes: 1

Related Questions