Arcadio Arcadio - 2 months ago 8
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

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

Comments