TSQL table based function returning different tables

Answer 1

You have to create a Multi-Statement table-based function. They work as standard table functions but are much like stored procedures.

Beware, though, you may suffer performance loss using multi statement functions.

Answer 2

Try this:

IF @DataSet = 'D1' BEGIN SELECT ... END
ELSE BEGIN SELECT ... END

Answer 3

Hi

I need this too...

The solution of Multi-Statement table-based function is good but not enough:

CREATE FUNCTION myProc (@ID Int)
   RETURNS @EmployeeList Table
     (  ID     Int
      , Name   nVarChar(50)
      , Salary Money
     )
 AS
   BEGIN
     IF @ID IS NULL
       BEGIN
         INSERT INTO @EmployeeList (ID, Name, Salary)
         SELECT ID, Name, Salary
         FROM Employee
       END
     ELSE
       BEGIN
         INSERT INTO @EmployeeList (ID, Name, Salary)
         SELECT ID, Name, Salary
         FROM Employee
         WHERE ID = @ID
       END
     RETURN
   END
 GO

One must d e f i n e a return table so that a predefined field must be returned and not any table like

if @tableNum=1
then select * from tableA --(tableA and tableB are completely differnt )
else 
select * from tableB