I want to create a function which will return table, fetch rows from different synonym based on @Parameter

Let say I have 3 synonyms

• snTable1
• snTable2
• snTable3

I want to create a function to fetch rows based on the parameter passed

fxFromTable (@FromDate, @ToDate, 'snTable1')  
  will return table from "Select * from snTable1 (@FromDate, @ToDate)" 

or

fxFromTable (@FromDate, @ToDate, 'snTable2')  
  will return table from "Select * from snTable2 (@FromDate, @ToDate)" 

or

fxFromTable (@FromDate, @ToDate, 'snTable3')  
  will return table from "Select * from snTable3 (@Fro开发者_运维问答mDate, @ToDate)" 

I guess this could be done with executing dynamic sql. But, it can't be used inside a function. So, a stored proc can be used in this case. This could be done something like below.

  CREATE PROCEDURE TEST
  (
       @TableName VARCHAR(50),
       @FromDate  DATETIME,
       @ToDate    DATETIME
  )
  AS
  BEGIN
       EXEC('SELECT * FROM ' + @TableName + ' WHERE FromDate = '''+ @FromDate + ''' AND ToDate = ''' + @ToDate + ''')
  END

If you want to use this in the other stored procedure, you can do something like below:

 INSERT @temp EXECUTE TEST

Now, @temp can be used as a normal temp table.

Hope this helps!!

You can create an inline TVF using UNION ALL. The last parameter represents the source type (1 = Sales.SalesOrderHeader, 2 = Sales.SpecialOffer, 3 = HumanResources.Employee).

CREATE FUNCTION dbo.GetData( @From DATETIME, @To DATETIME, @Source TINYINT )
RETURNS TABLE
AS
RETURN
SELECT  @Source AS [Source], soh.SalesOrderID BusinessObjectID, soh.OrderDate AS [Date], soh.SalesOrderNumber AS [Descriptor]
FROM    Sales.SalesOrderHeader soh
WHERE   @Source = 1
AND     soh.OrderDate BETWEEN @From AND @To
UNION ALL
SELECT  @Source , so.SpecialOfferID, so.StartDate, so.[Description]
FROM    Sales.SpecialOffer so
WHERE   @Source = 2
AND     so.StartDate >= @From 
AND     so.EndDate <= @To
UNION ALL
SELECT  @Source , e.BusinessEntityID, e.HireDate, e.NationalIDNumber
FROM    HumanResources.Employee e
WHERE   @Source = 3
AND     e.HireDate BETWEEN @From AND @To;
GO

Usage:

SELECT  *
FROM    dbo.GetData('20030101', '20031231', 3);

If you look at execution plan you will see something nice: instead of executing 3 SELECT statements, SQL Server will execute only one SELECT statement, in this case only the last SELECT:

StmtText
  |--Compute Scalar(DEFINE:([Union1007]=[AdventureWorks2008].[HumanResources].[Employee].[BusinessEntityID] as [e].[BusinessEntityID], [Union1008]=CONVERT_IMPLICIT(datetime,[AdventureWorks2008].[HumanResources].[Employee].[HireDate] as [e].[HireDate],0), [Union1009]=[AdventureWorks2008].[HumanResources].[Employee].[NationalIDNumber] as [e].[NationalIDNumber]))
       |--Clustered Index Scan(OBJECT:([AdventureWorks2008].[HumanResources].[Employee].[PK_Employee_BusinessEntityID] AS [e]), WHERE:([AdventureWorks2008].[HumanResources].[Employee].[HireDate] as [e].[HireDate]>='2003-01-01 00:00:00.000' AND [AdventureWorks2008].[HumanResources].[Employee].[HireDate] as [e].[HireDate]<='2003-12-31 00:00:00.000'))

This example is based on AdventureWorks2008 database.