Arcadio Arcadio - 10 months ago 36
SQL Question

SQL Server: sending parameter to function - parameter is name of table



I'd like to create function that gives me back numbers of rows depends on parameter which is name of table. Is it possible ? If yes, how to change the code:


CREATE FUNCTION MyFunction ( @sometable NVARCHAR(50) )
RETURNS TABLE
AS
RETURN SELECT COUNT(*) N_ROWS FROM @sometable


Thank you for answer.

Answer Source

You need to use dynamic query for this like

declare @sql nvarchar(250);
set @sql = 'SELECT COUNT(*) AS N_ROWS FROM ' + @sometable;
exec(@sql);

But again, you are trying to create a FUNCTION and you can't have a dynamic query in function. You may want to convert that to a stored procedure