none
Using CASE Statement to SET variable

    Question

  • Hey guys,

    I haven't found a way to SET a variable subject to values from joined tables. Any ideas?

    @ClientID is passed as a parameter.

     

    DECLARE @ClientID int<br/>
    SET @ClientID = 65<br/>
    <br/>
    <br/>
    DECLARE @ClientName VARCHAR(50)<br/>
    SET @ClientName = SELECT <br/>
    (CASE<br/>
    	WHEN BusinessInfo.TradingName != '' THEN BusinessInfo.TradingName<br/>
    	WHEN BusinessInfo.LegalEntityName != '' THEN BusinessInfo.LegalEntityName<br/>
    	ELSE Contact.Surname + ', ' + Contact.FirstName<br/>
    END)<br/>
    FROM	Client.Client LEFT OUTER JOIN<br/>
    	General.BusinessInfo ON Client.BusinessInfoID = BusinessInfo.BusinessInfoID LEFT OUTER JOIN<br/>
    	Client.Contact ON Contact.ContactID = Client.PrefContactID<br/>
    WHERE	Client.ClientID = @ClientID
    
    Cheers

     

    Tuesday, February 15, 2011 12:47 AM

Answers

  • You can assign the variable value directly with a SELECT statement or using a subquery:

    DECLARE @ClientID int;
    SET @ClientID = 65;
    
    DECLARE @ClientName VARCHAR(50);
    SELECT @ClientName =
    	CASE
    		WHEN BusinessInfo.TradingName <> '' THEN BusinessInfo.TradingName
    		WHEN BusinessInfo.LegalEntityName <> '' THEN BusinessInfo.LegalEntityName
    		ELSE Contact.Surname + ', ' + Contact.FirstName
    	END
    FROM	Client.Client 
    LEFT OUTER JOIN	General.BusinessInfo ON 
    	Client.BusinessInfoID = BusinessInfo.BusinessInfoID 
    LEFT OUTER JOIN	Client.Contact ON 
    	Contact.ContactID = Client.PrefContactID
    WHERE Client.ClientID = @ClientID;
    
    DECLARE @ClientID int;
    SET @ClientID = 65;
    
    DECLARE @ClientName VARCHAR(50);
    SET @ClientName =
    	(SELECT
    		CASE
    			WHEN BusinessInfo.TradingName <> '' THEN BusinessInfo.TradingName
    			WHEN BusinessInfo.LegalEntityName <> '' THEN BusinessInfo.LegalEntityName
    			ELSE Contact.Surname + ', ' + Contact.FirstName
    		END
    		FROM	Client.Client 
    		LEFT OUTER JOIN	General.BusinessInfo ON 
    			Client.BusinessInfoID = BusinessInfo.BusinessInfoID 
    		LEFT OUTER JOIN	Client.Contact ON 
    			Contact.ContactID = Client.PrefContactID
    		WHERE Client.ClientID = @ClientID
    	);
    

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by mastercerebral Tuesday, February 15, 2011 1:06 AM
    Tuesday, February 15, 2011 1:02 AM

All replies

  • You can assign the variable value directly with a SELECT statement or using a subquery:

    DECLARE @ClientID int;
    SET @ClientID = 65;
    
    DECLARE @ClientName VARCHAR(50);
    SELECT @ClientName =
    	CASE
    		WHEN BusinessInfo.TradingName <> '' THEN BusinessInfo.TradingName
    		WHEN BusinessInfo.LegalEntityName <> '' THEN BusinessInfo.LegalEntityName
    		ELSE Contact.Surname + ', ' + Contact.FirstName
    	END
    FROM	Client.Client 
    LEFT OUTER JOIN	General.BusinessInfo ON 
    	Client.BusinessInfoID = BusinessInfo.BusinessInfoID 
    LEFT OUTER JOIN	Client.Contact ON 
    	Contact.ContactID = Client.PrefContactID
    WHERE Client.ClientID = @ClientID;
    
    DECLARE @ClientID int;
    SET @ClientID = 65;
    
    DECLARE @ClientName VARCHAR(50);
    SET @ClientName =
    	(SELECT
    		CASE
    			WHEN BusinessInfo.TradingName <> '' THEN BusinessInfo.TradingName
    			WHEN BusinessInfo.LegalEntityName <> '' THEN BusinessInfo.LegalEntityName
    			ELSE Contact.Surname + ', ' + Contact.FirstName
    		END
    		FROM	Client.Client 
    		LEFT OUTER JOIN	General.BusinessInfo ON 
    			Client.BusinessInfoID = BusinessInfo.BusinessInfoID 
    		LEFT OUTER JOIN	Client.Contact ON 
    			Contact.ContactID = Client.PrefContactID
    		WHERE Client.ClientID = @ClientID
    	);
    

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by mastercerebral Tuesday, February 15, 2011 1:06 AM
    Tuesday, February 15, 2011 1:02 AM
  • DECLARE @ClientID int
    SET @ClientID = 65
    
    DECLARE @ClientName VARCHAR(50)
    SELECT @ClientName=
    (CASE
    	WHEN BusinessInfo.TradingName != '' THEN BusinessInfo.TradingName
    	WHEN BusinessInfo.LegalEntityName != '' THEN BusinessInfo.LegalEntityName
    	ELSE Contact.Surname + ', ' + Contact.FirstName
    END)
    FROM	Client.Client 
    LEFT OUTER JOIN General.BusinessInfo ON Client.BusinessInfoID = BusinessInfo.BusinessInfoID 
    LEFT OUTER JOIN Client.Contact ON Contact.ContactID = Client.PrefContactID
    WHERE	Client.ClientID = @ClientID
    
    Tuesday, February 15, 2011 1:06 AM