-1

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?

0
4

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.