Search code examples
sqlsql-serverfunctiontable-functions

Send string into function and use string as "table"

Is there a way to send in a string into a T-SQL function and use that string as a "table"

For instance

CREATE FUNCTION [dbo].[TEST] ( @id int, **@table_name** nvarchar(50))
RETURNS @mytables TABLE
   (
    id     int,
    values nvarchar(50)
   )
AS
BEGIN
   INSERT @mytables
      SELECT id, values FROM **@table_name**
   RETURN
END
Solution

  • You can't use dynamic SQL in function, also can't insert, update or delete from any table in user defined function (Please check this link given by Marc), for your requirements, SP is best solution like this way :

    CREATE PROCEDURE [dbo].[TEST] (@id int, @table_name nvarchar(50))
AS
BEGIN
    declare @strSQL NVARCHAR(MAX)

    set @strSQL = ' SELECT ' + cast(@id as varchar(20)) + ', Name from ' + @table_name
    exec(@strSQL)
END

    and run that SP by 

    EXEC [TEST] @id=5, @table_name='tablename'