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)
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;