Case when exists then select

I have requirement to select the field from the table in case statement like instead of some static value.

开发者_运维技巧

WHEN EXISTS(SELECT c.customer_name FROM Sales.Customer AS c 
            WHERE c.PersonID = @BusinessEntityID) 

           THEN c.customer_name  

How can this be achieved or is this possible . I have taken the following from msdn site. Need to tweak to fulfill my requirement.

USE AdventureWorks2008R2;
GO
CREATE FUNCTION dbo.GetContactInformation(@BusinessEntityID int)
RETURNS @retContactInformation TABLE 
(
BusinessEntityID int NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
ContactType nvarchar(50) NULL,
PRIMARY KEY CLUSTERED (BusinessEntityID ASC)
) 
AS 
-- Returns the first name, last name and contact type for the specified contact.
BEGIN
DECLARE 
    @FirstName nvarchar(50), 
    @LastName nvarchar(50), 
    @ContactType nvarchar(50);

-- Get common contact information
SELECT 
    @BusinessEntityID = BusinessEntityID, 
    @FirstName = FirstName, 
    @LastName = LastName
FROM Person.Person 
WHERE BusinessEntityID = @BusinessEntityID;

SET @ContactType = 
    CASE 
        -- Check for employee
        WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e 
            WHERE e.BusinessEntityID = @BusinessEntityID) 
            THEN 'Employee'

        -- Check for vendor
        WHEN EXISTS(SELECT * FROM Person.BusinessEntityContact AS bec
            WHERE bec.BusinessEntityID = @BusinessEntityID) 
            THEN 'Vendor'

        -- Check for store
        WHEN EXISTS(SELECT * FROM Purchasing.Vendor AS v          
            WHERE v.BusinessEntityID = @BusinessEntityID) 
            THEN 'Store Contact'

        -- Check for individual consumer
        WHEN EXISTS(SELECT * FROM Sales.Customer AS c 
            WHERE c.PersonID = @BusinessEntityID) 
            THEN 'Consumer'
    END;

-- Return the information to the caller
IF @BusinessEntityID IS NOT NULL 
BEGIN
    INSERT @retContactInformation
    SELECT @BusinessEntityID, @FirstName, @LastName, @ContactType;
END;

RETURN;
END;
GO

---

No idea what the rest of your code looks like, but typically this would be:

SELECT name = COALESCE(c.customer_name, o.other_entity_name)
  FROM dbo.MainEntity AS m
  LEFT OUTER JOIN dbo.Customers  AS c ON m.something = c.something
  LEFT OUTER JOIN dbo.OtherTable AS o ON m.something = o.something;

But other than a general idea, you haven't given quite enough information to supply a complete answer.