2
\$\begingroup\$

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?

\$\endgroup\$
  • \$\begingroup\$ 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 \$\endgroup\$ – Paul Feb 6 '18 at 16:29
  • \$\begingroup\$ Sorry I did not understand what you said. \$\endgroup\$ – J M Feb 7 '18 at 6:29
  • \$\begingroup\$ @Paul thank you for commenting, I tried that one, but its not working. \$\endgroup\$ – J M Feb 7 '18 at 6:48
  • \$\begingroup\$ Your self-answer below is exactly what I just said. \$\endgroup\$ – Paul Feb 7 '18 at 8:14
  • \$\begingroup\$ you said using 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
1
\$\begingroup\$

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
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.