March 29, 2011 at 7:10 am
I have a condition where I needed to create a Table variable and insert into that table. Now I need to create a cursor that loops through the rows in another result set and when it finds a match between my table variable and the result set, it grabs the values.
In the following stored proc if a menu_id does not exist in my table variable, there is no record for that menu_id returned. However, I want all the records returned and if a record has info in my Table variable, I want it to be included. I understand I can modify the following stored proc using cursors to accommodate this.
Any idea?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Faye.Fouladi>
-- Create date: <03/25/2011>
-- Description: <Get Nutrional Facts regarding daily Menu Items>
-- =============================================
ALTER PROCEDURE [dbo].[sp_Nutritional_facts] @Menu_date DateTime, @CategoryName varchar(50)
AS
DECLARE @NutrionTable TABLE
(
Nutrition_Facts_Id Numeric,
Calories varchar(20),
Total_Carbohydrate varchar(20),
Protein varchar(20),
Fiber varchar(20),
Total_Fat varchar(20),
Cholesterol varchar(20),
Sodium varchar(20)
)
INSERT INTO @NutrionTable (Nutrition_Facts_Id, Calories, Total_Carbohydrate,Protein,Fiber, Total_Fat,Cholesterol, Sodium)
SELECT NF.Nutrition_Facts_ID,
CASE
WHEN NF.Nutrition_Id = 1 Then (Select Nutrition_Value As 'Calories')
ELSE NULL
END,
CASE
WHEN NF.Nutrition_Id = 2 Then (Select Nutrition_Value As 'Total Carbohydrate')
ELSE NULL
END,
CASE
WHEN NF.Nutrition_Id = 3 Then (Select Nutrition_Value As 'Protein')
ELSE NULL
END,
CASE
WHEN NF.Nutrition_Id = 4 Then (Select Nutrition_Value As 'Fiber')
ELSE NULL
END,
CASE
WHEN NF.Nutrition_Id = 5 Then (Select Nutrition_Value As 'Total Fat')
ELSE NULL
END,
CASE
WHEN NF.Nutrition_Id = 6 Then (Select Nutrition_Value As 'Cholesterol')
ELSE NULL
END,
CASE
WHEN NF.Nutrition_Id = 7 Then (Select Nutrition_Value As 'Sodium')
ELSE NULL
END
FROM dbo.Nutritional_Facts NF
INNER JOIN dbo.s_Nutrition_Items NI ON NF.Nutrition_Id = NI.Nutrition_ID
--Select * From @NutrionTable
select CategoryType.Category_Type_Id,CategoryType.Category_Name,
Menu_Item_Category.Menu_Item_Name,Menu_Item.Menu_Name,
Menu_Type.Menu_Type_Name, Menu_Item.Menu_Item_Price,
Menu_Item.Menu_Item_Id,CategoryType.Start_HoursOfOpr,
Footer_Description,
CategoryType.End_HoursOfOpr,
max(nt.Calories) As Colries,
max(nt.Cholesterol) As Cholesterol,
max(nt.Protein) As Protein,
max(nt.Sodium) As Sodium ,
max(nt.Total_Carbohydrate) As Total_Carbohydrate,
max(nt.Total_fat) As Total_fat
from CategoryType,Menu_Item_Category,Menu_Item,Menu_Detail,Menu_Type, Nutritional_Facts, @NutrionTable nt
where CategoryType.Category_Type_Id=Menu_Item_Category.Category_Type_Id
and Menu_Item_Category.Menu_Item_Category_Id=Menu_Item.Menu_Item_Category_Id
and Menu_Type.Menu_Type_Id=Menu_Item.Menu_Type_Id
and Menu_Item.Menu_Item_Id=Menu_Detail.Menu_Item_Id
and Nutritional_Facts.Nutrition_Facts_ID = nt.Nutrition_Facts_ID
and Menu_Item.Menu_Item_Id=Nutritional_Facts.Menu_Item_Id
and Menu_Detail.Menu_date=convert(varchar(10), @Menu_date, 101)
and Menu_Item.Active_In='Y'
and CategoryType.Category_Name=@CategoryName
Group BY CategoryType.Category_Type_Id,CategoryType.Category_Name,
Menu_Item_Category.Menu_Item_Name,Menu_Item.Menu_Name,
Menu_Type.Menu_Type_Name, Menu_Item.Menu_Item_Price,
Menu_Item.Menu_Item_Id,CategoryType.Start_HoursOfOpr,
Footer_Description,
CategoryType.End_HoursOfOpr, Category_Order
order by Category_Order, Menu_Item_Name, Menu_Name
Execute sp_Nutritional_facts '03/25/2011', 'lunch'
March 29, 2011 at 7:33 am
that's what an outer join is all about.
In your case I would use a left join. It will return NULLS for its column values for the non-matching rows
I see you're still using old school joins 😉
The outer join syntax for old school joins is nolonger supported since sql2005.
...
from CategoryType
inner join Menu_Item_Category
on CategoryType.Category_Type_Id = Menu_Item_Category.Category_Type_Id
inner join Menu_Item
on Menu_Item_Category.Menu_Item_Category_Id = Menu_Item.Menu_Item_Category_Id
inner join Menu_Detail
on Menu_Item.Menu_Item_Id = Menu_Detail.Menu_Item_Id
inner join Menu_Type
on Menu_Type.Menu_Type_Id = Menu_Item.Menu_Type_Id
inner join Nutritional_Facts
on Menu_Item.Menu_Item_Id = Nutritional_Facts.Menu_Item_Id
LEFT join @NutrionTable nt
on Nutritional_Facts.Nutrition_Facts_ID = nt.Nutrition_Facts_ID
where Menu_Detail.Menu_date = convert(varchar(10), @Menu_date, 101)
and Menu_Item.Active_In = 'Y'
and CategoryType.Category_Name = @CategoryName
Group BY CategoryType.Category_Type_Id
, CategoryType.Category_Name
, Menu_Item_Category.Menu_Item_Name
, Menu_Item.Menu_Name
, Menu_Type.Menu_Type_Name
, Menu_Item.Menu_Item_Price
, Menu_Item.Menu_Item_Id
, CategoryType.Start_HoursOfOpr
, Footer_Description
, CategoryType.End_HoursOfOpr
, Category_Order
order by Category_Order
, Menu_Item_Name
, Menu_Name
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 29, 2011 at 9:51 am
Thanks for the prompt reply. I thought about this solution myself. However, my boss said the answer was in using cursors. I used outer Join and reformatted the entire join statement and it worked.
March 29, 2011 at 2:08 pm
Tank your for the feedback.
I did a good deed today 😀
Regarding cursors: In many cases cursors can be and should be avoided.
SSC has got a couple of good article and forum threads regarding cursor avoidance.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply