May 6, 2008 at 10:15 am
I have a stored procedure which I am trying to add info from a different table. There is a column where someone can choose what meal they would like beef, chicken or fish. Right now it's pulling the info but it shows them as choosing all 3 instead of the one they checked the box for in the form. How would I make it only choose the dinner they checked the box for?
/*********************************************************************************************************
* Type : Procedure
* Name : spGetCourseEmailList1
* Input Parameters :@Code1
* Output Parameters :None
* Purpose : This stored procedure gets the Members for a particular Chapter Event the cached items.
**********************************************************************************************************/
CREATE PROCEDURE [dbo].[spGetCourseEmailList1]
(
@Code1 char(9)
)
AS
SELECT DISTINCT
dbo.[names].lname as LastName,
dbo.[names].fname as FirstName,
dbo.[evser].ses as MealChoice,
dbo.[evldg].paid as AmountPaid,
dbo.[names].gp as PreferredAddress,
dbo.[names].mi as MiddleInitial,
dbo.[names].nname as NickName,
dbo.[names].xname as Suffix,
dbo.[names].hphone as HomePhone,
dbo.[names].email as EmailAddress,
dbo.[names].addr1 as HomeAddress1,
dbo.[names].addr2 as HomeAddress2,
dbo.[names].city as City,
dbo.[names].st as State,
dbo.[names].zip as ZipCode,
dbo.[firms].fname1 as FirmName1,
dbo.[firms].fname2 as FirmName2,
dbo.[firms].faddr1 as FirmAddress1,
dbo.[firms].faddr2 as FirmAddress2,
dbo.[firms].fcity as FirmCity,
dbo.[firms].fst as FirmState,
dbo.[firms].fzip as FirmZip,
dbo.[firms].fphone as FirmPhone,
dbo.[names].udflist1
FROM dbo.[firms]
INNER JOIN dbo.[names]
ON dbo.[firms].firm = dbo.[names].firm
INNER JOIN dbo.evldg
ON dbo.[names].id = dbo.[evldg].id
INNER JOIN dbo.evreg
ON dbo.[evldg].id = dbo.[evreg].id
LEFT OUTER JOIN dbo.evser
ON dbo.[evreg].code1 = dbo.[evser].code1
WHERE dbo.[evldg].code1 = @Code1 AND dbo.[evreg].code1 = @Code1 AND dbo.[names].xwebflag <> 'Y'
ORDER BY dbo.[names].lname, dbo.[names].fname
GO
May 6, 2008 at 11:07 am
I can't see anything about a meal in this code. What am I missing?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 6, 2008 at 11:17 am
The error in your code is that you are not limiting the code for the meal choice table to only the checked one. This can be a good proc to use either a CTE or a case statement on. Use of a case statement is as follows:
CASE
WHEN MLChicken = Somevalue THEN ChickenYes
WHEN MLBeef = Somevalue then BeefYes
WHEN MLFish = Somevalue the FishYes
ELSE NULL
END mealchoice,
The else statement is not mandatory, but just good coding so that you know the case statement was hit.
Happy coding
Marvin Dillard
Senior Consultant
Claraview Inc
May 6, 2008 at 12:04 pm
But what if the next meeting the meal choice changes? Like they have beef, fish and salad? It could change and I don't want to be locked into only those 3 meals. Thanks for the help!!!!!
May 6, 2008 at 12:22 pm
IN that case I would make my checkboxes generic. That being done, you can then pass the
needed information in the same type of case statement after setting the values when calling
the stored procedure
CREATE usp_Meals (@option1, @option2, @option3 and so on)
CASE
WHEN choice1 IS NOT NULL THEN @option1
WHEN option2 IS NOT NULL THEN @option2
WHEN choice3 IS NOT NULL THEN @option3
END MealChoice
Then when you call your stored proc say the first time you could use
EXEC usp_Meals ('Beef', 'Chicken', 'Steak')
which will set @option1 to Beef, @option2, Chicken, @option3 to Steak.
The next time you call the proc and the choices change you will use something like
EXEC usp_Meals('Turkey', 'Fish', 'Salad')
which will set the @option values as above dependent on position.
That keeps your code dynamic.
Remember, think generic as much as possible to allow reuse of code
Marvin Dillard
Senior Consultant
Claraview Inc
May 6, 2008 at 1:24 pm
Thanks, but I think the problem with this is, I don't know when the stored procedure will be called next. We have chapters all over the state of FL and when someone signs up for their next chapter meeting the chapter will log into the site and pull the list for their roster.
May 6, 2008 at 2:15 pm
What is the structure of your ses table that you are pulling the meal choice from?
Marvin Dillard
Senior Consultant
Claraview Inc
May 6, 2008 at 2:26 pm
Actually I would need to see the structure of all of the tables used in that query, I see you are using an outer join when setting the meal choice. Which may be your problem. I won't be able to tell without seeing the complete table structure
Marvin Dillard
Senior Consultant
Claraview Inc
May 6, 2008 at 2:40 pm
I'll have to send this to you tomorrow. I'm right in the middle of something. I do appreciate your help!
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply