sql selecting from different tables based on boolean

Question

hi,

I have a simple stored procedure like so:

ALTER PROCEDURE [dbo].[spList_Report] 
  @id INT


AS 
  SET NOCOUNT ON 

  SELECT * 
  FROM 
    tblProducts as products 
  WHERE  
    product.intID = @id  

I have 2 user tables: MainUser and SubUser Both tables have a foreign key column productID which is related to the primary key intID of the product table intID. They both also have a column emailAddress. The product table also has a bit column isMainUser.

How can I update my stored procedure to return the column emailAddress based on the isMainUser value? So if the value is true it selects the email address from the MainUser table and if its false then it selects the emailAddress from the SubUser table.

E.g what I want is only one emailAddress column:

ALTER PROCEDURE [dbo].[spList_Report] 
  @id INT


AS 
  SET NOCOUNT ON 

  SELECT 
    products.* 
  , myUser.emailAddress
  FROM 
    tblProducts as products 
  WHERE  
    product.intID = @id  

Answer 1

You have left out some important information. I am assuming that

• Products can be joined with MainUser on a UserProductID
• Products can be joined with SubUser on a UserProductID
• There is 1 Main user for each product. (most likely not but that will need to be adressed when you have given us more information)
• There is 1 Sub user for each product.

SQL Statement

  SELECT  products.*  
          , CASE WHEN isMainUser=1 
            THEN MainUser.emailAddress 
            ELSE SubUser.emailAddress 
            END
  FROM    tblProducts as products  
          LEFT OUTER JOIN MainUser mu ON mu.UserProductID = products.UserProductID
          LEFT OUTER JOIN SubUser su ON su.UserProductID = products.UserProductID      
  WHERE   product.intID = @id   

---

Note that it is considered bad practice to use a SELECT *. SELECT * should never be present in production code.

Answer 2

Create a view on the two tables and join to that in your proc