T-SQL Stored Procedure Question?
Posted on 2011-03-08
I have some problem understanding the following stored procedure. Could you guys explain in detail what the following stored procedure does?
CREATE PROCEDURE Test_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)
else if exists (select 1 from RecipeIngredients where RecipeID = @RecipeID and IngredientName = @IngredientName)
update RecipeIngredients set quantity = @Quantity where RecipeID = @RecipeID and IngredientName=@IngredientName
INSERT INTO RecipeIngredients(RecipeID, quantity,IngredientName, UnitofMeasurement)
VALUES (@RecipeID, @Quantity, @IngredientName,@UnitofMeasurement)
select @Output = STUFF( (select CONVERT(VARCHAR,quantity) + ' ' + UnitofMeasurement + ' ' + IngredientName + ''
where RecipeID = @RecipeID FOR XML PATH ('')) ,1,0, '')