sql-server add tag
SE Anon or Wiki user (imported from SE)
Trying to create a function that returns a table. I want to pass the table's name as parameter.

This is what I tried:

    create or alter function cust (@T NVARCHAR(250) )
     returns table 
    return (select * from @T)

But getting an error.

What can be done?
Top Answer
SE Anon or Wiki user (imported from SE)
> What can be done?

Nothing. Table names can't be parameters unless you use dynamic SQL, 
and dynamic SQL can't be used inside of a function.

You could use a stored procedure:

    CREATE OR ALTER PROCEDURE dbo.SelectWhatever (@SchemaName sysname, @TableName sysname)
    DECLARE @SafeSchema sysname = N'',
            @SafeTable sysname = N'',
            @SQL NVARCHAR(MAX) = N'';
    SELECT @SafeSchema = SCHEMA_NAME(t.schema_id),
           @SafeTable  = t.name
    FROM sys.tables AS t
    WHERE t.schema_id = SCHEMA_ID(ISNULL(@SchemaName, 'dbo'))
    AND   t.name = ISNULL(@TableName, 'SomeKnownTable');
    Maybe do some NULL checks of @SafeSchema and @SafeTable here
    SET @SQL += N'
    SELECT TOP (100) *
    FROM ' + QUOTENAME(@SafeSchema) 
           + N'.'
           + QUOTENAME(@SafeTable)
           + N';';
    RAISERROR('%s', 0, 1, @SQL) WITH NOWAIT;
    EXEC sys.sp_executesql @SQL;

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.