July 11, 2008 at 9:25 am
Help anyone!
I am a super newbie working with the Northwind db. Learning code (if - else) statement. Code below appears to never take the else path. Instead returns 93 records where each one contains 'Honcho' and no records contain "Peon'.
I suspect I do not understand some basic concept about SELECT. Namely, does select return a set or does it process sequentially one record from the table, at a time. It appears to be a set since all 93 records contain 'Honcho'. Yet an IF-ELSE construct would imply sql processes records one at a time.
I cannot proceed until I understand IF- ELSE processing. And I think I need a good answer on the set or single step processing confusion. Thanks for any help.
Victor Victor
CREATE PROCEDURE HonchoOrPeon
AS
BEGIN
SET NOCOUNT ON;
declare @currenttitle varchar(30)
declare @newtitle varchar(30)
select @currenttitle = contacttitle from customers
if @currenttitle = 'Owner'
begin
select @newtitle = 'Honcho'
end
else
begin
select @newtitle = 'Peon'
end
select @newtitle as newtitle from customers
END
GO
July 11, 2008 at 9:41 am
I think the problem is actually this line, and the others like it:
select @currenttitle = contacttitle from customers
There's no Where clause on there. That means it will always end up being the last row in the table. If this is being run repeatedly to try to get multiple rows, it will always just get the last row.
- 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
July 11, 2008 at 9:45 am
Aha! I understand your answer. However, I do want the logic to apply to all the records in the table and that is why I did not put a WHERE clause on the select statement.
July 11, 2008 at 9:52 am
BTW. Thanks for your reply. A bit more on my confusion about what you get from a select statement. I assumed that the select statement without a where clause functioned like the delete statement, where without a where clause the entire table is deleted. VV
July 11, 2008 at 10:38 am
I took GSquared's suggestion and added a WHERE clause to both select statements. Get exactly the same result as before -- All 93 records returned contain 'Honcho' and none contain 'Peon'.
Help anyone please.......
July 11, 2008 at 10:40 am
Oops. The Where clause I added to both select statements was one to guarantee that every record in the table would be processed. That is: WHERE contacttitle <> 'qwerrty23456'.
July 11, 2008 at 3:08 pm
The way to get the result you're looking for is:
select
case contacttitle
when 'Owner' then 'Honcho'
else 'Peon'
end as Newtitle
from customers
"If" is a flow control, not a data function.
- 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
July 11, 2008 at 3:17 pm
Got it: G**2. Your suggestion works. Thanks.
Victor Victor
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply