Apply if only on one column on result

This are sample template

UserDetails : id,UserName,IsShow
Brand       : id,BrandName
Product     : id,ProductName
BrandProduct:id,brandid,productid,price

If Isshow is false, show entire price else hide second -fourth digit.(Price is 5 digits)

Query :

create procedure Sp_Details
@brandid int,
@productid int,
@IsShow bit
as
begin
        select B.Brandname,P.Producetname,Bp.Price
        from BrandProduct Bp
        inner join B
        on Bp.brandid = B.id
        inner join P
        on Bp.productid = P.id
        where brandid = @brandid
        and productid = @productid 
end

I don’t want to use case, isnull or any user created functions because that works row by row. Here @Isshow is either false or true, not specific to each record, it is the same for all records.

Apart from dynamic sql or writing same query twice with if else.. is there any other way to achieve where don’t have to write same query twice? Because a few queries are bigger in size.

If(@Isshow = false)
begin
     select B.Brandname,P.Producetname,Bp.Price
        from BrandProduct Bp
        inner join B
        on Bp.brandid = B.id
        inner join P
        on Bp.productid = P.id
        where brandid = @brandid
        and productid = @productid 
end
else
begin
      select B.Brandname,P.Producetname, REPLACE(Price, (SUBSTRING(Price,2, 3)), 'xxx')
        from BrandProduct Bp
        inner join B
        on Bp.brandid = B.id
        inner join P
        on Bp.productid = P.id
        where brandid = @brandid
        and productid = @productid 
end

Answer

select
    B.Brandname,
    P.Producetname,
    REPLACE(Price, (SUBSTRING(Price,2, 3*@Isshow)), 'xxx')
from
    BrandProduct Bp
    inner join B on Bp.brandid = B.id
    inner join P on Bp.productid = P.id
where
    brandid = @brandid
    and productid = @productid

Advertisement

Answer