T-SQL stored procedure help required

Hi all,

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.


Receipe  Table                                  
ReceipeID      Name           Date         AuthorID         createddate      
1               Sandwich        1/1/2011         1               1/1/2011      
2               Ham Burger      1/1/2011         2               1/1/2011      
                                   
                                   
                     
RecipeAuthor  Table                                  
AuthorID     AuthorName                            
1                   irene                              
2                   walsh                              
                                   
                             
ReciepeIngredients Table                                  
ReciepeIngredientsID  ReceipeID    Ingredeint Name   UnitofMeasurement      quantity          
1                                       1                         milk                    cup                         2
2                                       1                         butter                 cups                        3
3                                        1                       White sugar         tablespoon              2
                             
ReceipeDirection                                    
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


)
AS
BEGIN

if exists (select 1 from RecipeIngredients where RecipeID = @RecipeID and IngredientName = @IngredientName and

Quantity = @Quantity)
     return -1

if exists (select 1 from RecipeIngredients where RecipeID = @RecipeID and IngredientName = @IngredientName)
begin
     update RecipeIngredients set Quantity = @Quantity where RecipeID = @RecipeID and IngredientName =

@IngredientName
     return 0
end

INSERT INTO RecipeIngredients(RecipeID, Quantity,IngredientName, UnitofMeasurement)
VALUES     (@RecipeID, @Quantity, @IngredientName,@UnitofMeasurement)

return 0

select @Output = coalesce(@Output + ';', '') + UnitOfMeasurement + ' ' + IngredientName
from RecipeIngredients
where RecipeID = @RecipeID
END



declare @output varchar(max)

exec dbo.Recipe_Stored_Procedure
@RecipeID = 1, @Quantity = 2, @IngredientName = 'vanilla extract', @UnitofMeasurement = 'cups',
@output = @output out


Do you have any ideas what is wrong with the stored procedure?
Itudk_2010Asked:
Who is Participating?
 
raulggonzalezCommented:
Hi,
I've tried your SP with your data sample, and I know why you always get null...

Your last query returns more than 1 row, so the @Output vble doesn't get the value (because it's not a single value) ...

Try


select @Output = STUFF( (select coalesce(@Output + ';', '') + UnitofMeasurement + ' ' + IngredientName + ','
from RecipeIngredients
where RecipeID = @RecipeID FOR XML PATH ('')) ,1,0, '')

This way you will concat in a single value all the ingredients of the given recipe...

cheers




0
 
rushShahCommented:
try this,


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
)
AS
BEGIN

if exists (select 1 from RecipeIngredients where RecipeID = @RecipeID and IngredientName = @IngredientName and
Quantity = @Quantity)
begin
     return -1
end
else if exists (select 1 from RecipeIngredients where RecipeID = @RecipeID and IngredientName = @IngredientName)
begin
     update RecipeIngredients set Quantity = @Quantity where RecipeID = @RecipeID and IngredientName=@IngredientName
     return 0
end
else
begin
	INSERT INTO RecipeIngredients(RecipeID, Quantity,IngredientName, UnitofMeasurement)
	VALUES     (@RecipeID, @Quantity, @IngredientName,@UnitofMeasurement)
	return 0
end

select @Output = coalesce(@Output + ';', '') + UnitOfMeasurement + ' ' + IngredientName
from RecipeIngredients
where RecipeID = @RecipeID
END
GO

Open in new window

0
 
Itudk_2010Author Commented:
Hi rushShah,

Thanks for your hlp. The stored procedure creation work fine but how to execute it to call the insert or update in the stored procedure above. When I execute the following code, it gives me NULL result.

declare @output varchar(max)
exec uspRecipe_StoredProcedure
@RecipeID = 1, @Quantity = 2, @IngredientName = 'vanilla extract', @UnitofMeasurement = 'cups',
@output = @output out
select @output


According to the following question how to change the above code to complete this 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).


Any ideas?

I really appreciate your help.
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
raulggonzalezCommented:
Hi,

please try

declare @output varchar(max)
exec uspRecipe_StoredProcedure
@RecipeID = 1, @Quantity = 2, @IngredientName = 'vanilla extract', @UnitofMeasurement = 'cups',
@output = @output OUTPUT
select @output


http://msdn.microsoft.com/en-us/library/ms188332.aspx


Good luck
0
 
raulggonzalezCommented:
Apart from that ...

When you execute

declare @output varchar(max)
exec uspRecipe_StoredProcedure
@RecipeID = 1, @Quantity = 2, @IngredientName = 'vanilla extract', @UnitofMeasurement = 'cups',
@output = @output OUTPUT
select @output

You always get the first case where you inly return -1

when you write in your SP return -1 you should execute it like this

declare @output varchar(max)
declare @RETVAL INT
exec @RETVAL = uspRecipe_StoredProcedure
@RecipeID = 1, @Quantity = 2, @IngredientName = 'vanilla extract', @UnitofMeasurement = 'cups',
@Output = @output OUTPUT
select @output, @RETVAL

@RETVAL will keep the return value, so you know where you are everytime you execute it ...

So review the logic inside your SP and make sure that it does exactly what you want...

Cheers and luck.
0
 
Itudk_2010Author Commented:
hI raulggonzalez,

Thank you very much for your efforts. It is still giving me "NULL and -1 result" by using the code as follows. I don't have that much knowledge of stored procedures. If you could solve this problem that would be great.

declare @output varchar(max)
declare @RETVAL INT
exec @RETVAL = uspRecipe_StoredProcedure
@RecipeID = 1, @Quantity = 2, @IngredientName = 'vanilla extract', @UnitofMeasurement = 'cups',
@Output = @output OUTPUT
select @output, @RETVAL


Looking forward to your reply.
0
 
Itudk_2010Author Commented:
hI raulggonzalez,

When I add new values for the fields like this, then I get NULL and 0.

declare @output varchar(max)
declare @RETVAL INT
exec @RETVAL = uspTestRecipe_StoredProcedure
@RecipeID = 5, @Quantity = 5, @IngredientName = 'blah blah test', @UnitofMeasurement = 'tttcups',
@Output = @output OUTPUT
select @output, @RETVAL


According to the question above 0 means success and I understand that point. But how to achieve the following part of the question for example the error message and print statement etc?

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).

Looking forward to your reply.
0
 
Itudk_2010Author Commented:
hI raulggonzalez,


How to print the following message for example by executing the stored procedure?

"2 cups white sugar;1/2 cup butter;1/2 cup milk;3 tablespoons cocoa powder"
0
 
raulggonzalezCommented:
Hi again,

But you have to understand that when you say return, you shut the execution and return the value specified...

So, it doesn't matter what happens you always return a value, so the last query WILL NEVER BE EXECUTED, so @Output WILL BE NULL

Other thing.. the returning value by default when there's no error is 0, so you don't have to return 0 unless you want to stop the execution (and that is not really nice)

Apart from that, you're missing the quantity in your query to show your desired result...

try


select STUFF( (select  CONVERT(VARCHAR,quantity) + ' ' + UnitofMeasurement + ' ' + IngredientName + ','
from RecipeIngredients
---where RecipeID = @RecipeID
FOR XML PATH ('')) ,1,0, '')

And you'll get it...

Hope this helps...

Cheers

 
0
 
Itudk_2010Author Commented:


I also found out that the following code statement in the above stored procedure is not executed at all. I can't figure it out

select @Output = coalesce(@Output + ';', '') + UnitOfMeasurement + ' ' + IngredientName
from RecipeIngredients
where RecipeID = @RecipeID
0
 
Itudk_2010Author Commented:
hI raulggonzalez,

Thanks again. What do you mean by STUFF in this code? select STUFF( (select  CONVERT(VARCHAR,quantity) + ' ' + UnitofMeasurement + ' ' + IngredientName + ','
from RecipeIngredients
---where RecipeID = @RecipeID
FOR XML PATH ('')) ,1,0, '')

And also The requirement does not allow me to use XPATH.t
Sorry for the trouble.
0
 
raulggonzalezCommented:
Hi

http://msdn.microsoft.com/en-us/library/ms188043.aspx

I wrote the solution using XML PATH, because the last query returns more than 1 value and combining STUFF and FOR XML PATH you can concatenate them in a single value.

If what you want is to return a recordset, just execute the last query without @output = and you will get as many rows as returned by the query, but it won't look like the string you described above...


When for many different rows I have to get a single string, I always use the combination FOR XML PATH and STUFF ... I learnt it here in EE and it's really useful for cases like yours.

If not, you always can execute the last query in a CURSOR (not recommended AT ALL) and concatenate current value to your output vble...

Cheers

0
 
Itudk_2010Author Commented:


I tried the following sample stored procedure and it gives me the output according to the question asked but of course the main part is missing about insert and update.

If you could look at it and may be you can find a solution. This is really annoying and I am really sorry for the trouble and I really appreciate your help.

Here it is

CREATE PROCEDURE TESTING
(
@RecipeID int,            ---- recipe ID
@Quantity decimal,        -----ingredient quantity
@IngredientName varchar(1000), ----- ingredient name
@UnitofMeasurement varchar(1000), ----ingredient unit
@Output varchar(max) output

)

AS
BEGIN
select @Output = coalesce(@Output + ';', '') + UnitOfMeasurement + ' ' + IngredientName
from RecipeIngredients
where RecipeID = RecipeID


END

/* executingthe follow stored procedure give correct out put */

declare @Output varchar(max)
declare @RETVAL INT
exec @RETVAL = TESTING
@RecipeID = 1, @Quantity = 1, @IngredientName = 'blah blah test', @UnitofMeasurement = 'tttcups',
@Output = @Output OUTPUT
select @Output, @RETVAL


Any ideas to improve this one?

Thanks a lot.
0
 
Itudk_2010Author Commented:
hI raulggonzalez,

I also modified the stored procedure using the STUFF and XPATh etc but still I get NULL and 0.
0
 
raulggonzalezCommented:
Hi,

I'm going to post all the tests I did... and I get the correct result...

Let me know..

Cheers
DROP TABLE Receipe
DROP TABLE RecipeAuthor
DROP TABLE RecipeIngredients 
DROP TABLE RecipeIngredients 
DROP TABLE RecipeDirection

CREATE TABLE Recipe(ReceipeID INT, 
					[Name] VARCHAR (100),
					Date DATETIME,
					AuthorID INT,
					createddate DATETIME)
INSERT INTO Recipe
VALUES(1,'Sandwich', '1/1/2011' , 1 , '1/1/2011')      
INSERT INTO Recipe
VALUES(2, 'Ham Burger', '1/1/2011', 2,'1/1/2011')      
                                    
                                    
                      
CREATE TABLE RecipeAuthor  (                                 
AuthorID     INT ,
AuthorName  VARCHAR(100) )                          

INSERT INTO RecipeAuthor
VALUES(1 ,'irene' )                              
INSERT INTO RecipeAuthor
VALUES(2,'walsh' )                              
                                    
                             
CREATE TABLE RecipeIngredients (
RecipeIngredientsID INT, 
RecipeID   INT, 
IngredientName  VARCHAR(100), 
UnitofMeasurement     VARCHAR(100), 
quantity  INT)         

INSERT INTO RecipeIngredients 
VALUES (1,1,'milk','cup',2)
INSERT INTO RecipeIngredients  
VALUES (2,1,'butter','cups',3)
INSERT INTO RecipeIngredients  
VALUES (3,1,'White sugar','tablespoon',2)


CREATE TABLE RecipeDirection
(ReceipeStepsID INT,
ReceipeID INT,
StepProcess VARCHAR(100))                
INSERT INTO RecipeDirection
VALUES(1,1,'wash the greens')                 
INSERT INTO RecipeDirection
VALUES(2,1,'toast the bread')                 
INSERT INTO RecipeDirection
VALUES(3,1,'apply cheese on bread')    


--- SP 

ALTER 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
)
AS
BEGIN

if exists (select 1 from RecipeIngredients where RecipeID = @RecipeID and IngredientName = @IngredientName and
quantity = @Quantity)
begin
     return -1
end
else if exists (select 1 from RecipeIngredients where RecipeID = @RecipeID and IngredientName = @IngredientName)
begin
     update RecipeIngredients set quantity = @Quantity where RecipeID = @RecipeID and IngredientName=@IngredientName
     --return 0
end
else
begin
	INSERT INTO RecipeIngredients(RecipeID, quantity,IngredientName, UnitofMeasurement)
	VALUES     (@RecipeID, @Quantity, @IngredientName,@UnitofMeasurement)
	--return 0
end


select @Output = STUFF( (select coalesce(@Output + ';', '') + UnitofMeasurement + ' ' + IngredientName + ','
from RecipeIngredients
where RecipeID = @RecipeID FOR XML PATH ('')) ,1,0, '')


END
GO

-- The call 


declare @output varchar(max)
declare @RETVAL INT
exec @RETVAL = uspRecipe_StoredProcedure
@RecipeID = 1, @Quantity = 2, @IngredientName = 'cinnamon', @UnitofMeasurement = 'tsp',
@Output = @output OUTPUT
select @output, @RETVAL

Open in new window

SPresult.jpg
0
 
Itudk_2010Author Commented:
hI raulggonzalez,

Cool, that worked perfectly, thanks a lot. But I have some question for you to explain it a bit so it gets clear to me.

- First of all why I need a Return value which is of course a sucees according to the question above but why?
- How to print error messages in the stored procedure according to the question above?
- Could you explain what the whole stored procedure is doing? Because I got this stored procedure in parts in this forum and I am bit confused that how it works and also I have little knowledge of stored procedures?
- Could you also explain in detail what the following part is doing in the stored procedure?

select @Output = STUFF( (select coalesce(@Output + ';', '') + UnitofMeasurement + ' ' + IngredientName + ','
from RecipeIngredients
where RecipeID = @RecipeID FOR XML PATH ('')) ,1,0, '')


I know that is too much explaining but I am really sorry about that. I really appreciate your help.

Looking forward to your reply.
0
 
Itudk_2010Author Commented:
One last question?

Where to include the Quantity variable in the following code?

select @Output = STUFF( (select coalesce(@Output + ';', '') + UnitofMeasurement + ' ' + IngredientName + ','
from RecipeIngredients
where RecipeID = @RecipeID FOR XML PATH ('')) ,1,0, '')

0
 
raulggonzalezCommented:
ok,

point by point ..

- First of all why I need a Return value which is of course a sucees according to the question above but why?
As I guess, this SP is meant to be called from some other piece of software .. probably a web page or user interface, right? .. So from where you call it, after the SP execution you should verify that everything was right in the sp, to display a message according to what happened.

- How to print error messages in the stored procedure according to the question above?
You don't need to "print" literally ... If so and you want to print, you can use the PRINT statement and you'll see your printed messages in the tab 'Messages' in SQL SERVER Management Studio...

- Could you explain what the whole stored procedure is doing? Because I got this stored procedure in parts in this forum and I am bit confused that how it works and also I have little knowledge of stored procedures?
come'on... it's your sp :-))  ... The sp does what you want... if your question is how it's better you to try to understand part by part ..


- Could you also explain in detail what the following part is doing in the stored procedure?
-- this takes multiples rows(ingredients) and returns a XML string format
select CONVERT(VARCHAR,quantity) + ' ' + UnitofMeasurement + ' ' + IngredientName + ';'
from RecipeIngredients
FOR XML PATH ('')


-- And STUFF from msdn
-- The STUFF function inserts a string into another string.
-- It deletes a specified length of characters in the first string at the start position
--and then inserts the second string into the first string at the start position.

-- Or basically put the XML in a char datatype
select STUFF( (select CONVERT(VARCHAR,quantity) + ' ' + UnitofMeasurement + ' ' + IngredientName + ';'
from RecipeIngredients FOR XML PATH ('')) ,1,0, '')



and now the query


select @Output = STUFF( (select CONVERT(VARCHAR,quantity) + ' ' + UnitofMeasurement + ' ' + IngredientName + ''
from RecipeIngredients
where RecipeID = @RecipeID FOR XML PATH ('')) ,1,0, '')



 Cheers
0
 
Itudk_2010Author Commented:
hI raulggonzalez,


Perfect. One last question about a query. Of course I will accept your previous solution and this one as well.

Here is the query question:

What is the name of all recipes that do not use egg as an ingredient?

And I have tried the following but it still lists egg.

select RecipeName from Recipe where RecipeID not in
(select RecipeID from Recipeingredients where IngredientName = 'egg')

Could you make corrections in this query?

Thanks a lot.
0
 
raulggonzalezCommented:
It's very strange...

the query looks fine and I tried it and it returns fine.

Are you sure it's working wrong?

double check the data in those tables and calculate manually what you should have as a result...


If it's not working, post all the rows you have in both tables and I'll have a look.


cheers.



0
 
Itudk_2010Author Commented:
I have only two recipes. And yes I double checked it, it gives me the Names of the recipies only. The egg is found in only one recipe but not the other?

Query:

select RecipeName from Recipe where RecipeID not in
(select RecipeID from Recipeingredients where IngredientName = 'egg')



Rows from RecipeIngredient table:

INSERT INTO RecipeIngredients
VALUES(1, 1,'butter softened', 'cup',  1)

INSERT INTO RecipeIngredients
VALUES(2, 1, 'packed brown sugar', 'cups', 2)

INSERT INTO RecipeIngredients
VALUES(3, 1, 'eggs', '',  2)


Rows from Recipe table:

INSERT INTO Recipe
VALUES(1,'Kitchen Sink Cookies', '1/1/2011' , 1 , '1/1/2011')      
INSERT INTO Recipe
VALUES(2, 'No Bake Cookies II', '1/1/2011', 2,'1/1/2011')      
                                   
I appreciate your time and help.
0
 
raulggonzalezCommented:
... :-((((

Man, you really must be more careful with your spelling (LOL)

select RecipeName from Recipe where RecipeID not in
(select RecipeID from Recipeingredients where IngredientName = 'egg') -- EGG !!!

INSERT INTO RecipeIngredients
VALUES(3, 1, 'eggs', '',  2) -- EGGS

EGG <> EGGS !!!!!!!!!!!!


Other thing is you say

where IngredientName LIKE  'egg%'

Try this

select RecipeName from Recipe where RecipeID not in
(select RecipeID from Recipeingredients where IngredientName LIKE 'egg%')

but seriously, programming can become hell if you mistype names, datatypes and so on...

Because your logic can be well thought but a small mistake will drive you crazy.


Don't forget to check this JIC
http://msdn.microsoft.com/en-us/library/ms179859.aspx


Cheers
0
 
Itudk_2010Author Commented:
Excellent Man. Yes that was a spelling mistake sorry about that LOL.

I really appreciate your help and efforts.

Thanks a lot
0
 
Itudk_2010Author Commented:
Perfect.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.