Solved

T-SQL stored procedure help required

Posted on 2011-03-05
24
550 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:Itudk_2010
  • 14
  • 9
24 Comments
 
LVL 8

Expert Comment

by:rushShah
ID: 35044329
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
 

Author Comment

by:Itudk_2010
ID: 35044574
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
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 35045869
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
 
LVL 8

Accepted Solution

by:
raulggonzalez earned 500 total points
ID: 35045945
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
 
LVL 8

Assisted Solution

by:raulggonzalez
raulggonzalez earned 500 total points
ID: 35046020
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
 

Author Comment

by:Itudk_2010
ID: 35046130
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
 

Author Comment

by:Itudk_2010
ID: 35046143
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
 

Author Comment

by:Itudk_2010
ID: 35046156
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
 
LVL 8

Assisted Solution

by:raulggonzalez
raulggonzalez earned 500 total points
ID: 35046183
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
 

Author Comment

by:Itudk_2010
ID: 35046190


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
 

Author Comment

by:Itudk_2010
ID: 35046196
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
 
LVL 8

Assisted Solution

by:raulggonzalez
raulggonzalez earned 500 total points
ID: 35046213
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Itudk_2010
ID: 35046228


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
 

Author Comment

by:Itudk_2010
ID: 35046232
hI raulggonzalez,

I also modified the stored procedure using the STUFF and XPATh etc but still I get NULL and 0.
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 35046255
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
 

Author Comment

by:Itudk_2010
ID: 35046457
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
 

Author Comment

by:Itudk_2010
ID: 35046501
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
 
LVL 8

Assisted Solution

by:raulggonzalez
raulggonzalez earned 500 total points
ID: 35046737
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
 

Author Comment

by:Itudk_2010
ID: 35046841
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
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 35046929
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
 

Author Comment

by:Itudk_2010
ID: 35046953
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
 
LVL 8

Assisted Solution

by:raulggonzalez
raulggonzalez earned 500 total points
ID: 35046980
... :-((((

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
 

Author Comment

by:Itudk_2010
ID: 35047097
Excellent Man. Yes that was a spelling mistake sorry about that LOL.

I really appreciate your help and efforts.

Thanks a lot
0
 

Author Closing Comment

by:Itudk_2010
ID: 35047102
Perfect.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now