Schema as a parameter in a function-CodePudding

Is it possible to have a schema as a parameter in a function such as

CREATE FUNCTION get_data_for_weekly_front_end_collections_summary(@schema VARCHAR)
RETURNS TABLE
AS RETURN
(
    SELECT *
    FROM @schema.db1
)

The error:

CodePudding user response：

TSQL doesn't support parametrizing object names in queries, and user-defined functions don't support dynamic SQL. But you can do something like this:

CREATE FUNCTION get_data_for_weekly_front_end_collections_summary(@schema NVARCHAR(200))
RETURNS TABLE
AS RETURN
(

    select *
    from a.db1
    where @schema = 'a'
    union all
    select * 
    from b.db1
    where @schema = 'b'
)