hnakum
asked on
store procedure
how can i write create store 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).
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).
RGBDart
Can u post here script for tables, which contains recipies?
ASKER
This is all i have right now.
/* main table */
create table RECIPES (
recipe_name varchar(255) NOT NULL primary key,
/* type = Kitchen_Sink_Cookies or No_Bake_Cookies */
recipe_type varchar(255) NOT NULL,
submitted_by varchar(255),
reference varchar(max)
)
/* INGREDIENTS table , define FK to RECIPES on recipe_name */
/* recipe_name + step are PK */
create table INGREDIENTS (
recipe_name varchar(255) NOT NULL,
step int NOT NULL,
ingredient varchar(max) NOT NULL
)
/* DIRECTIONS table , define FK to RECIPES on recipe_name */
/* recipe_name + step are PK */
create table DIRECTIONS (
recipe_name varchar(255) NOT NULL,
step int NOT NULL,
direction varchar(max) NOT NULL
)
/* main table */
create table RECIPES (
recipe_name varchar(255) NOT NULL primary key,
/* type = Kitchen_Sink_Cookies or No_Bake_Cookies */
recipe_type varchar(255) NOT NULL,
submitted_by varchar(255),
reference varchar(max)
)
/* INGREDIENTS table , define FK to RECIPES on recipe_name */
/* recipe_name + step are PK */
create table INGREDIENTS (
recipe_name varchar(255) NOT NULL,
step int NOT NULL,
ingredient varchar(max) NOT NULL
)
/* DIRECTIONS table , define FK to RECIPES on recipe_name */
/* recipe_name + step are PK */
create table DIRECTIONS (
recipe_name varchar(255) NOT NULL,
step int NOT NULL,
direction varchar(max) NOT NULL
)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
no one has helped me yet.