Send string into function and use string as "table"

Question

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

No, you cannot do this directly - if you need to parametrize the table and/or column names, you must resort to using dynamic SQL - no other way to do it — marc_s
– marc_s, Commented Nov 27, 2013 at 10:23
I mean, I could use another function inside this function or whatever it doesn't matter. I just want to know if it can be done and how :) — Joe
– Joe, Commented Nov 27, 2013 at 10:25
Plus: a function in SQL Server cannot have any side effects on the database, e.g. you cannot insert, update or delete rows of data inside a function. See CREATE FUNCTION - section "Limitations and Restrictions": User-defined functions cannot be used to perform actions that modify the database state. — marc_s
– marc_s, Commented Nov 27, 2013 at 10:26

Upendra Chaudhari · Accepted Answer · 2013-11-27 10:36:45Z

1

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'

answered Nov 27, 2013 at 10:36

Upendra Chaudhari

6,5515 gold badges28 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Joe Over a year ago

I guess this is the only way. The only problem is that the str can only be 4000 char and I need it to be much bigger. Thanks anyways!

Upendra Chaudhari Over a year ago

where you faced problem of 4000 chars ? In string you just pass table name, right ?

Joe Over a year ago

This was just an example. The one I want to create is much larger than that :) BTW can i skip the declare @strSQL and just to a SELECT directly in the store procedure??

Upendra Chaudhari Over a year ago

Then you can use VARCHAR(MAX) or NVARCHAR(MAX) for large strings

Joe Over a year ago

Yes but my max is 4000 characters. Could I create a store procedure and mix your solution with just plain select like declare (at)strSQL NVARCHAR(MAX) set (at)strSQL = ' SELECT ' + cast((at)id as varchar(20)) + ', Name from ' + (at)table_name exec((at)strSQL) UNION ALL select id, name from table2

|

Collectives™ on Stack Overflow

Send string into function and use string as "table"

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related