How do sql can understand or convert a nvarchar is name of a table in sql server

Question

I Waint run code in SQL SERVER

ALTER FUNCTION return_table (@table nvarchar(250))
RETURNS  TABLE 
AS
RETURN
(
     SELECT * FROM @table
)

none using PROCEDURE . THANK FOR HELP

what are a way for help this ? but using function . Thank you — Lương Tuấn Anh, Jul 17 '14 at 08:43
You can't. The only (semi-sane) way to parameterize table names is to use dynamic SQL, which you're not allowed to do inside functions — Damien_The_Unbeliever, Jul 17 '14 at 09:07

score 0 · Answer 1 · edited May 23 '17 at 12:13

A function requires an explicit definition of the return value type, e.g. the columns being returned. The SQL statement you provided will not work in such a manner for the following reasons:

ALTER FUNCTION return_table (@table nvarchar(250)) -- You have declared a parameter of type table, this is not the right way of doing this
RETURNS  TABLE  -- you haven't defined the structure of the table being returned, this needs explicitly defining
AS
RETURN
(
     SELECT * FROM @table -- using SELECT * is bad practice as the structure of the table may change and then conflict with the structure of the table being returned
)

The first part of the problem is declaring a parameter of type TABLE; this question has good examples on how to get around doing this. Quick summary: you need to declare the table as a type before passing in the type as the parameter to your function:

CREATE TYPE MyTableParameterDefinition AS TABLE (
   [ColumnName] NVARCHAR(250) NULL
)

This type can then be passed as a parameter to your function:

CREATE FUNCTION myFunctionName (
  @TableVariable MyTableParameterDefinition READONLY
)...--INSERT CODE HERE--

I'm uncertain whether you can use a similar method for the return type and would suggest against this given the implication of the contract defined by the function. Better practice would be to define the structure of the table being returned and explicitly select the columns from the table:

ALTER FUNCTION return_table (
    @table MyTableParameterDefinition
) 
RETURNS  TABLE 
(
    -- Explicitly define columns returned by the function
   [ColumnName] NVARCHAR(250) NULL
)
AS
RETURN
(
     SELECT 
        [ColumnName]
     FROM 
        @table 
)

Thank talegna. I understand your ideal , but i want my function can using all table in database, if my database have more 100 tables. I think will have many problems for this function ex: edit column or data_type. I want have a function can use for all my table and can use it on other query in sql. — Lương Tuấn Anh, Jul 17 '14 at 10:31
Going by your description what you are wanting to do is not possible unless all the tables have the same format. For the sort of thing you are wanting to do it's more likely that the use of Dynamic SQL but as @Damien_The_Unbeliever said cannot be done in functions. You also need to remember that the return format of a function MUST be explicitly defined, you cannot return a generic table as that would not be a function, a `Stored Procedure` would be your only option for returning all entries in a table in such a manner and even then it is bad coding practice. — talegna, Jul 17 '14 at 10:35
oh. Thank talegna. i will think a way other for this problem. but i like one function or procedure can using for multiple table :( and can check error more easy. this time. if using procedure for it. I can't check error for query. Optimize SQL statement is bester — Lương Tuấn Anh, Jul 18 '14 at 04:27
sorry , My skill for English very bad :D may be you can't understand what i sad :D but Thank you so much! — Lương Tuấn Anh, Jul 18 '14 at 04:29

score 0 · Accepted Answer · answered Jul 18 '14 at 06:22

The only way to make this work is truly horrible for both aesthetic and (probably) performance reasons. It also assumes that all tables that might be passed as parameters have the same structure:

ALTER FUNCTION return_table (@table sysname)
RETURNS  TABLE 
AS
RETURN
(
     SELECT * FROM TableA where @table = 'TableA'
     UNION ALL
     SELECT * FROM TableB where @table = 'TableB'
     UNION ALL
     SELECT * FROM TableC where @table = 'TableC'
     /* add more UNIONs and SELECTs for each table that you want to use */
)

How do sql can understand or convert a nvarchar is name of a table in sql server

2 Answers2