Problem with IF statement 2

[logidude]

Hi everyone,

I'm trying to write a Table-valued function that returns a different set of data for a person depending on a boolean parameter.

I'm trying to achieve something like this...with an if statement to inverse the order of my coalesce depending on if I want primarly unicode values or not. I've done it with a case statement but it require a case per column which is not ideal.

create function dbo.GetInfo(@ID int, @languageID int, @bOrder bit)

	returns table
	as
	return (
	select 
		IF (bOrder = 1)
			BEGIN
			coalesce(firtName, firstNameUNIcode) as firstName,
			coalesce(lastName, lastNameUNIcode) as lastName
			END
		ELSE
		coalesce(firstNameUNIcode, firstName,) as firstName,
		coalesce(lastNameUNIcode, lastName) as lastName
	FROM
		tblPerson
		LEFT OUTER JOIN tblPersonUNICODE on tblPerson.personID = tblPersonUNICODE.fkPersonID
		AND tblPersonUNICODE.fkLangID = @languageID 
	WHERE tblPerson.personID = @ID
	)

It doesn't work at all, throwing errors at the IF statement, are you not allowed to break the flow of a select statement to select different info?

Thanks for the help!

 

[gmmastros]

No. Yes. Well.... kinda. But not the way you're doing it. The easiest thing for you to do is to use CASE/WHEN instead. like this.

create function dbo.GetInfo(@ID int, @languageID int, @bOrder bit)

    returns table
    as
    return (
    select 
        Case When bOrder = 1 
             Then coalesce(firtName, firstNameUNIcode)
             Else coalesce(firstNameUNIcode, firstName)
             END As FirstName,
        Case When bOrder = 1
             Then coalesce(lastName, lastNameUNIcode)
             Else coalesce(lastNameUNIcode, lastName)

             End As LastName
    FROM
        tblPerson
        LEFT OUTER JOIN tblPersonUNICODE on tblPerson.personID = tblPersonUNICODE.fkPersonID
        AND tblPersonUNICODE.fkLangID = @languageID 
    WHERE tblPerson.personID = @ID
    )

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[logidude]

Hi George,

Thanks for the reply!

It does work with a case, but it evaluate the logical statement more than once. I wanted to group them together in one go and only have one condition to evaluate.

Looks like that might not be an option...?

 

[ousoonerjoe]

Instead of using IF, look at CASE WHEN. See example below:

create function dbo.GetInfo(@ID int, @languageID int, @bOrder bit)

    returns table
    as
    return (
    select 
		[FirstName] = CASE WHEN bOrder = 1 THEN 
							coalesce(firtName, firstNameUNIcode)
						ELSE
							coalesce(firstNameUNIcode, firstName)
						END,
		[LastName] = CASE WHEN bOrder = 1 THEN
							coalesce(lastName, lastNameUNIcode)
						ELSE
							coalesce(lastNameUNIcode, lastName)
						END		
    FROM
        tblPerson
        LEFT OUTER JOIN tblPersonUNICODE on tblPerson.personID = tblPersonUNICODE.fkPersonID
        AND tblPersonUNICODE.fkLangID = @languageID
    WHERE tblPerson.personID = @ID
    )

--------------------------------------------------
"...and did we give up when the Germans bombed Pearl Harbor? NO!"

"Don't stop him. He's roll'n."
--------------------------------------------------

 

[bborissov]

That can't be true!!!!
I saw something wrong in GEORGE'S code!!!
I'm gong to buy lotto ticket right now!

Copy and paste problem

What is bOrder = 1?
Isn't it @bOrder = 1?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[logidude]

@bborissov , hehe I didn't even notice.

 

[gmmastros]

Oh man.

it evaluate the logical statement more than once

You're right. It evaluates the logical statement twice. The difference in performance here is NOT going to make a difference.

The alternative would be to use IF within the function, like this:

create function dbo.GetInfo(@ID int, @languageID int, @bOrder bit)

    returns @Output table (FirstName nvarchar(100), LastName nvarchar(100))
    as

	Begin
		If @bOrder = 1
	       Insert Into @Output(FirstName, LastName)
           Select coalesce(firstName, firstNameUNIcode),
                  coalesce(lastName, lastNameUNIcode)
           FROM
               tblPerson
               LEFT OUTER JOIN tblPersonUNICODE on tblPerson.personID = tblPersonUNICODE.fkPersonID
               AND tblPersonUNICODE.fkLangID = @languageID 
           WHERE tblPerson.personID = @ID
		Else	

	       Insert Into @Output(FirstName, LastName)
           Select coalesce(firstNameUNIcode, firstName),
                  coalesce(lastNameUNIcode, lastName)
           FROM
               tblPerson
               LEFT OUTER JOIN tblPersonUNICODE on tblPerson.personID = tblPersonUNICODE.fkPersonID
               AND tblPersonUNICODE.fkLangID = @languageID 
           WHERE tblPerson.personID = @ID

    return 

	End

As you can see, you need to repeat the entire query this time. In my opinion, this makes the code a little more difficult to maintain. The difference in performance will be negligible.

P.S. I'll give a star to the first person that identifies the other typo. (both typos are fixed in this code sample).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[logidude]

firtName?

Both were caused by me..........., lol.

 

[gmmastros]

As promised, you get a star!

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom