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?
left outer join
, but in that way on the two condition I get full list of itemcategory \$\endgroup\$ – J M Feb 7 '18 at 8:33