title: Using CASE outside SELECT in table-valued function
body: <p>I'm trying to select the same columns from a different table/view depending on the value of an argument (@ruleset). As it is not possible to pass the name of the table as a parameter nor to construct the name inside the function, used CASE structure outside the select statements. However, I get an error:
  "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
  [Hope I get this right, it is my first question here.]</p>
  
  <pre><code>CREATE FUNCTION app.fgProduct 
  (   
      @ruleset nvarchar(50),
      @matno nvarchar(50), 
      @datarevision int
  )
  RETURNS TABLE 
  AS
  RETURN 
  (
  SELECT
  CASE WHEN @ruleset = 'G1' THEN 
  (
    SELECT 
      @matno AS ProductId
      ,@datarevision AS DataRevision
      ,[ProductName]
    FROM [ruleset].[g1gxProduct]
    WHERE ProductId = @matno
  )
  WHEN @ruleset = 'G2' THEN 
  (
    SELECT 
      @matno AS ProductId
      ,@datarevision AS DataRevision
      ,[ProductName]
    FROM [ruleset].[g2gxProduct]
    WHERE ProductId = @matno
  )
  END
  )
  </code></pre>
  
  <p>There's a bunch of other views, so this whole issue cannot be solved in one procedure. Above is an example of a function which is used to generate new records based on various rule sets (= sets of views).</p>