Multiple values in a parameter

• kaj

  SSCommitted

  Points: 1605

  More actions

  February 1, 2023 at 6:57 pm

  #4144371

  vijay.verma2 wrote:

  Dear All,

  My one condition is working like if data exist than it should fail only one condition is pending that is "if data exists without qty, than qty should add in existing qty"

  I find your approach overly complicated, if you only want to insert one ingredient at a time, so I have devised a possible alternative.

  Not sure if I read your conditions correctly, but  anyway, check it out...

  ALTER PROCEDURE INSERT_INGREDIENTS
      @Recipes_Name VARCHAR(255),
      @INGREDIENTS_NAME VARCHAR(255),
      @QUANTITY VARCHAR(255),
      @UOM VARCHAR(20)
  AS
  BEGIN
  IF NOT EXISTS (
      SELECT *
      FROM INGREDIENTS I
      JOIN INGREDIENTS_NAME N     ON N.ID = I.INGREDIENTS_NAME_ID
      JOIN Recipes_Name R         ON R.ID = I.Recipes_Name_ID
      JOIN UOM U                  ON U.ID = I.UOM
      WHERE 
          N.INGREDIENTS_NAME = @INGREDIENTS_NAME
      AND R.NAME = @Recipes_Name
      AND U.UOM_NAM = @UOM
      )
      INSERT INTO INGREDIENTS (Recipes_Name_ID, INGREDIENTS_NAME_ID, QUANTITY, UOM,CREATED_BY, CREATED_DATE)
          SELECT 
              R.ID,
              (SELECT N.ID FROM INGREDIENTS_NAME N WHERE N.INGREDIENTS_NAME = @INGREDIENTS_NAME),
              @QUANTITY,
              (SELECT U.ID FROM UOM U WHERE U.UOM_NAME = @UOM),
              SYSTEM_USER,
              GETDATE()
          FROM Recipes_Name R
          WHERE 
              R.NAME = @Recipes_Name
  
  ELSE
      UPDATE I
      SET     I.QUANTITY = ISNULL(i.QUANTITY, '') + @QUANTITY
      FROM INGREDIENTS I
      JOIN INGREDIENTS_NAME N     ON N.ID = I.INGREDIENTS_NAME_ID
      JOIN Recipes_Name R         ON R.ID = I.Recipes_Name_ID
      JOIN UOM U                  ON U.ID = I.UOM
      WHERE
          N.INGREDIENTS_NAME = @INGREDIENTS_NAME
      AND R.NAME = @Recipes_Name
      AND U.UOM_NAME = @UOM
      AND (
              I.QUANTITY IS NULL
           OR I.QUANTITY = ''
           )
  
  
  IF @@Rowcount() = 1
      SELECT 'Ok' AS Outcome
  ELSE
      SELECT 'Failed' AS Outcome
  
  END
  

  • This reply was modified 1 year, 9 months ago by  kaj.
  • This reply was modified 1 year, 9 months ago by  kaj.
  • This reply was modified 1 year, 9 months ago by  kaj. Reason: typos, typos and yet another typo, sorry!
  • This reply was modified 1 year, 9 months ago by  kaj. Reason: changed/improved the first IF NOT EXISTS query slightly