Function that returns table with name passed as a parameter

Question

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?

Answer 1

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)
AS
BEGIN
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) *
/dbo.SelectWhatever/
FROM ' + QUOTENAME(@SafeSchema) 
       + N'.'
       + QUOTENAME(@SafeTable)
       + N';';
RAISERROR('%s', 0, 1, @SQL) WITH NOWAIT;
EXEC sys.sp_executesql @SQL;
END;

Erik Darling wrote a follow up article on this: Towards Safer Dynamic SQL.