CODEWITHSUNDEEP
sql-serversql-server-2008
Hardik Gondalia
Hardik Gondalia

Reputation: 3717

How to return Null in Case statement in SQL Server 2008

I want to return null value in case when. Here is my query

Declare @MasterProduct nvarchar(50) = N'Sharepoint Easy Nav',
        @ProductSlug nvarchar(50) = 'sharepoint-easynav-free',
        @CountryID bigint = 1111;

SELECT        
Product.ProductName, 
Product.MasterProductName, 
Price.ProductPrice, 
Price.ProductTax, 
Price.ProductTotalPrice, 
Product.TotalSiteCollectionText, 
Product.TotalSiteCollection, 
Product.Is_Support, 
Price.Is_Support_Price, 
Product.ProductSlug, 
Country.CountrySymbol, 
Country.CountryId
FROM
BR_Product as Product 
Inner Join BR_ProductPrice as Price on Product.ID = Price.ProductID 
left Join BR_Country as Country On Price.CountryID = Country.CountryId 
where Product.MasterProductName = IsNull(@MasterProduct, Product.MasterProductName) 
and Product.ProductSlug = IsNull(@ProductSlug, Product.ProductSlug) 
and Country.CountryId = case 
                          when (select count(BR_ProductPrice.ID) 
                                from BR_ProductPrice 
                                where  BR_ProductPrice.CountryID = @CountryID) > 0 
                          Then @CountryID 
                          else Null
                        end

It returns me no rows.
When I remove

Country.CountryId = case when (select count(BR_ProductPrice.ID) from BR_ProductPrice where BR_ProductPrice.CountryID = @CountryID) > 0 Then @CountryID else Null end

it Returns me following: enter image description here

I want CountryId compare null part of case statement in else section something like this

Then @CountryID else Country.CountryId is null

Query is working properly when I pass CountryID = 1101.

Upvotes: 1

Views: 2258

Answers (3)

Pream
Pream

Reputation: 537

Use ISNULL(CountryID,'') on where conditions and on conditions and replace = null with = '' 

Inner Join BR_ProductPrice as Price on Product.ID = Price.ProductID 
left Join BR_Country as Country On isnull(Price.CountryID,'') = isnull(Country.CountryId,'') 
where Product.MasterProductName = ISNULL(@MasterProduct, Product.MasterProductName) 
and Product.ProductSlug = ISNULL(@ProductSlug, Product.ProductSlug) 
and ISNULL(Country.CountryId,'') = case 
                      when (select count(BR_ProductPrice.ID) 
                            from BR_ProductPrice 
                            where  ISNULL(BR_ProductPrice.CountryID,'') = @CountryID) > 0 
                      Then @CountryID 
                      else ''
                    end

Upvotes: 1

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4191

Try to change this

from:

Country.CountryId = case 
                          when (select count(BR_ProductPrice.ID) 
                                from BR_ProductPrice 
                                where  BR_ProductPrice.CountryID = @CountryID) > 0 
                          Then @CountryID 
                          else Null
                        end

To:

Country.CountryId =(select Case when cnt_BR_ProductPriceID > 0 then @CountryID else null end from
(select count(BR_ProductPrice.ID) as cnt_BR_ProductPriceID from BR_ProductPrice where  BR_ProductPrice.CountryID = @CountryID) as cnt)

Never tried yet.

Hope it work.

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

You can't use = with null. Try this instead:

and (
    (Country.CountryId = @CountryID 
     and (select count(BR_ProductPrice.ID) 
          from BR_ProductPrice 
          where  BR_ProductPrice.CountryID = @CountryID) > 0 
    ) 
    OR Country.CountryId IS NULL
    )

Upvotes: 0

Related Questions