0

I have been using SQL Server for past one month and I need a suggestion from SQL Server folks to help me on this use case.

The tables below are just to explain about the idea that I am looking for.

I have tables in different schema like

MyDb.dbo.Festivals
MyDb.India.Festivals
MyDb.China.Festivals
MyDb.USA.Festivals

I am writing a table valued function without any schema prefixed in it like

CREATE FUNCTION getFestivals()
RETURNS TABLE
AS
RETURN
(
 SELECT * FROM festivals
)

As I haven't applied any schema, it defaults to dbo and creates the TVF as dbo.getFestivals(). Now I have created synonyms for all other schemas

CREATE SYNONYM India.getFestivals  FOR dbo.getFestivals; 
CREATE SYNONYM USA.getFestivals  FOR dbo.getFestivals; 

I tried to query like select * FROM MyDb.India.getFestivals() and still it returns the festivals from dbo.festivals and not india.festivals. I understand that though the synonyms were created it just executes the select query in the dbo schema context and not in india schema context.

I want suggestions on how to have a common table valued function that will query based on the schema prefixed, i.e. MyDB.India.getFestivals() should get festivals from India and MyDB.USA.getFestivals() should return festivals from USA.

Question

Is there a way I can have a table valued function that can query based on the schema context?

The only possible way I can think of is to create the same table valued function in all schemas.

Caveats

  1. I have to stick to table valued function only and the above use case is a sample scenario to explain my problem.
  • I have a feeling that you would get much better answers if you were to describe the base problem to be solved rather than a difficulty with a paticular solution you have developed. – Michael Green Dec 16 '17 at 10:27
  • @MichaelGreen, I have to query a database schema that takes daily snapshot of data for last 30 days. This is for reporting what were the sales and shipment as of yesterday, daybefore yesterday and today. The system doesn't have history lookup, and they do it this way. Since I have to pass dynamic paramters to , TableValue functions are the only options there and with the resutlts from TVF, further results will be massaged. So I was checking how to have schema agnostic TVF – napster Dec 16 '17 at 11:11
  • What does this mean ? "Since I have to pass dynamic paramters to , TableValue functions are the only options there" Perhaps describe more clearly why you have to use TVF? – Mister Magoo Dec 17 '17 at 12:40
3

Is there a way I can have a table valued function that can query based on the schema context?

No. The TVF is bound to the table at the time of creation, not at execute time, so you won't be able to dynamically access different tables from a single function.

The only possible way I can think of is to create the same table valued function in all schemas.

That would be the cleanest method. Another approach is to create a UNION ALL view like the example below and pass the desired schema as a parameter. This will not perform as well since all the referenced tables will need to be scanned.

CREATE VIEW dbo.vw_Festivals
AS
          SELECT 'dbo' AS SchemaName, Col1, Col2 FROM MyDb.dbo.festivals
UNION ALL SELECT 'India' AS SchemaName, Col1, Col2 FROM MyDb.India.festivals
UNION ALL SELECT 'China' AS SchemaName, Col1, Col2 FROM MyDb.China.festivals
UNION ALL SELECT 'USA' AS SchemaName, Col1, Col2 FROM MyDb.USA.festivals;
GO

CREATE FUNCTION getFestivals(@SchemaName sysname)
RETURNS TABLE
AS
RETURN
(
    SELECT Col1, Col2
    FROM vw_Festivals
    WHERE SchemaName = @SchemaName
);
GO
  • thanks for your suggestions. I really liked the approach of having unionall.. this is one nice way of looking at the problem, thank you very much. I will benchmark this and see if this fits me else I will go with TVF for schema – napster Dec 17 '17 at 12:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.