Inner Join only if satisfies If condition

Question

I'm looking for code review, and optimizations.

I have two tables ItemCategory and ShopItemCategory

ItemCategory

--------------------------------------------------
| No | ItemCategoryId  |      CategoryName       |
--------------------------------------------------
| 1  |    1233         |      Cement             |
--------------------------------------------------
| 2  |    1200         |      Drinks             | 
--------------------------------------------------

ShopItemCategory

--------------------------------------------------
| No | ItemCategoryId  |        ShopId           |
--------------------------------------------------
| 1  |    1233         |           25            |
--------------------------------------------------
| 2  |    1200         |           32            | 
--------------------------------------------------

I have an sp (stored procedure) to get ItemCategory list for dropdown,

CREATE PROCEDURE [dbo].[spGetItemCategory]

AS
BEGIN

    SET NOCOUNT ON;

    SELECT ItemCategoryId ,CategoryName   
    FROM ItemCategory

End

This one works fine, and now I have new requirement. I need to send ShopId to the sp and return ItemCategory list that satisfies the condition, and if the receiving shopid equals 0 then I need to return all the ItemCategory.

My sp :

CREATE PROCEDURE [dbo].[spGetItemCategory]
(               
    @ShopID INT
)

AS
BEGIN

     SET NOCOUNT ON;    

     if @ShopID <> 0
         BEGIN

              SELECT ItemCategory.ItemCategoryId,CategoryName   
              FROM ItemCategory 
              inner join ShopItemCategory on    
                     ShopItemCategory.ShopId = @ShopID AND  
                     ShopItemCategory.ItemCategoryId = ItemCategory.ItemCategoryId

          END
     else
     BEGIN

          SELECT  ItemCategoryId,CategoryName
          FROM aItemCategory

     END
 END

This works well in my code. But in the two conditions only the inner join part is different, So is there a way to reduce the code and improve performance?

I got other stored procedures with larger codes in if conditions, so this method will make it bigger and bigger. Is there any alternate way to do this? or to simplify this?

Answer 1

I did something like this

CREATE PROCEDURE [dbo].[spGetItemCategory] (@ShopID INT)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT ItemCategory.ItemCategoryID
        ,CategoryName
    FROM ItemCategory
    INNER JOIN ShopitemCategory ON (
            ShopitemCategory.shopid = @ShopID
            AND ShopitemCategory.itemcategoryid = ItemCategory.ItemCategoryID
            )
        OR (@ShopID = 0)
END