Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem with IF statement 2

Status
Not open for further replies.

logidude

Programmer
Aug 12, 2008
13
CA
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.

Code:
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!

 
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.

Code:
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
 
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...?
 
Instead of using IF, look at CASE WHEN. See example below:
Code:
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."
--------------------------------------------------
 
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.
 
Oh man. [hammer]

logidude said:
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:

Code:
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
 
firtName?

Both were caused by me..........., lol.
 
As promised, you get a star!

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top