trjhtr

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?

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