# Inner Join only if satisfies If condition

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?

• You can do it as 1 query by using a left outer join and adding "and (@Shopid = 0 or ShopID = @ShopID)" on your where statement. Whether this would be quicker, I doubt it – Paul Feb 6 '18 at 16:29
• Sorry I did not understand what you said. – J M Feb 7 '18 at 6:29
• @Paul thank you for commenting, I tried that one, but its not working. – J M Feb 7 '18 at 6:48
• Your self-answer below is exactly what I just said. – Paul Feb 7 '18 at 8:14
• you said using left outer join, but in that way on the two condition I get full list of itemcategory – J M Feb 7 '18 at 8:33

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