Newbie - Really simple code won't work - totally confused

  • 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

  • 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

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

  • 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

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

  • 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'.

  • 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

  • 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