CODEWITHSUNDEEP
sqlsql-serverfunctiontable-functions
Joe
Joe

Reputation: 3025

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

Upvotes: 0

Views: 218

Answers (1)

Upendra Chaudhari
Upendra Chaudhari

Reputation: 6543

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'

Upvotes: 1

Related Questions