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).
hnakumAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RGBDartCommented:
Can u post here script for tables, which contains recipies?
0
hnakumAuthor Commented:
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

)

0
hnakumAuthor Commented:
Hopefully this will help you.

Kitchen Sink Cookies

Submitted By: Cara

Reference: allrecipes.com (http://allrecipes.com/Recipe/Kitchen-Sink-Cookies/Detail.aspx)

 

INGREDIENTS

·          1 cup butter, softened

·          2 cups packed brown sugar

·          2 eggs

·          2 teaspoons vanilla extract

·          2 1/3 cups all-purpose flour

·          1 teaspoon baking soda

·          1 teaspoon salt

·          2 cups rolled oats

·          1 cup semisweet chocolate chips

·          1 cup vanilla baking chips

·          1/2 cup butterscotch chips

·          1 cup chopped pecans

 

DIRECTIONS

1.        Preheat the oven to 375 degrees F (190 degrees C).

2.        In a large bowl, cream together the butter and brown sugar until smooth. Beat in the eggs one at a time, then stir in the vanilla. Combine the flour, baking soda and salt; stir into the creamed mixture. Mix in the oats, chocolate chips, vanilla chips, butterscotch chips and chopped pecans. Drop by tablespoonfuls onto ungreased cookie sheets. Cookies should be at least 2 inches apart.

3.        Bake for 8 to 10 minutes in the preheated oven. Allow cookies to cool on baking sheet for 5 minutes before removing to a wire rack to cool completely.

 

No Bake Cookies II

Submitted By: Sandy

Reference: allrecipes.com (http://allrecipes.com/Recipe/No-Bake-Cookies-II/Detail.aspx)

 

INGREDIENTS

·          2 cups white sugar

·          1/2 cup butter

·          1/2 cup milk

·          3 tablespoons cocoa powder

·          3 cups quick cooking oats

·          1 teaspoon vanilla extract

 

DIRECTIONS

1.        Mix together sugar, butter or margarine, and milk in a saucepan. Bring to a boil and boil for one minute, stirring constantly.

2.        Remove from heat and mix in cocoa, quick oatmeal and vanilla. Drop by spoonfuls on waxed paper.

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hnakumAuthor Commented:
no one has helped me yet.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.