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, Nov 27 '13 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, Nov 27 '13 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"](http://msdn.microsoft.com/en-us/library/ms186755.aspx): *User-defined functions cannot be used to perform actions that modify the database state.* — marc_s, Nov 27 '13 at 10:26

score 1 · Accepted Answer · answered Nov 27 '13 at 10:36

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 '13 at 10:36

Upendra Chaudhari

6,473
5
25
42

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! – Joe Nov 27 '13 at 12:53
1

where you faced problem of 4000 chars ? In string you just pass table name, right ? – Upendra Chaudhari Nov 27 '13 at 13:02
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?? – Joe Nov 27 '13 at 13:37
Then you can use VARCHAR(MAX) or NVARCHAR(MAX) for large strings – Upendra Chaudhari Nov 27 '13 at 13:40
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 – Joe Nov 27 '13 at 13:46
@Joe: `NVARCHAR(MAX)` can hold up to **1 billion** characters - not just 4000 ... – marc_s Nov 27 '13 at 22:32
@marc_s: Can NVARCHAR(MAX) be used with sql 2000 as welL? and contain a billion characters? – Joe Nov 28 '13 at 08:41
@Joe: **no** - `NVARCHAR(max)` is a new feature in SQL Server **2005** (you didn't mention what **version** of SQL Server you're using - you should, with every question, just makes it easier for us to respond with appropriate advice). SQL Server 2000 is ancient history, I typically assume people are on 2005 or newer – marc_s Nov 28 '13 at 08:49
@marc_s: Ok yeah sorry about that! Thanks for the help though! To both of you! – Joe Nov 28 '13 at 08:56

Send string into function and use string as "table"

1 Answers1