T-SQL stored procedure help required
Posted on 2011-03-05
I need some help to create a stored procedure in answer to the following question:
Write a stored procedure that will add an ingredient to a recipe. The procedure should accept at a minimum the recipe name or ID, ingredient name or ID, ingredient quantity, and ingredient unit.
If the ingredient already exists, update the quantity; an error should occur if the new ingredient and quantity are the same. A return code of 0 should indicate success and -1 if an error was encountered.
Make use of an output parameter that contains a semi-colon separated list of ingredient name and units. The value should be structured similar to the following:
2 cups white sugar;1/2 cup butter;1/2 cup milk;3 tablespoons cocoa powder
Include at the bottom of the same file, SQL code that will execute the procedure and print or select the error code and ingredient list (output parameter).
Below is the table definitions with some sample data.
ReceipeID Name Date AuthorID createddate
1 Sandwich 1/1/2011 1 1/1/2011
2 Ham Burger 1/1/2011 2 1/1/2011
ReciepeIngredientsID ReceipeID Ingredeint Name UnitofMeasurement quantity
1 1 milk cup 2
2 1 butter cups 3
3 1 White sugar tablespoon 2
ReceipeStepsID ReceipeID StepProcess
1 1 wash the greens
2 1 toast the bread
3 1 apply cheese on bread
Here is the stored procedure code which I got from the replies of the experts here which still does not gives the correct output.
Stored procedure code:
CREATE PROCEDURE uspRecipe_StoredProcedure
@RecipeID int, ---- recipe ID
@Quantity decimal, -----ingredient quantity
@IngredientName varchar(1000), ----- ingredient name
@UnitofMeasurement varchar(1000), ----ingredient unit
@Output varchar(max) output
if exists (select 1 from RecipeIngredients where RecipeID = @RecipeID and IngredientName = @IngredientName and
Quantity = @Quantity)
if exists (select 1 from RecipeIngredients where RecipeID = @RecipeID and IngredientName = @IngredientName)
update RecipeIngredients set Quantity = @Quantity where RecipeID = @RecipeID and IngredientName =
INSERT INTO RecipeIngredients(RecipeID, Quantity,IngredientName, UnitofMeasurement)
VALUES (@RecipeID, @Quantity, @IngredientName,@UnitofMeasurement)
select @Output = coalesce(@Output + ';', '') + UnitOfMeasurement + ' ' + IngredientName
where RecipeID = @RecipeID
declare @output varchar(max)
@RecipeID = 1, @Quantity = 2, @IngredientName = 'vanilla extract', @UnitofMeasurement = 'cups',
@output = @output out
Do you have any ideas what is wrong with the stored procedure?