Search code examples
sql-serverudfdynamic-tables

Returning tables in UDF

I am new to SQL Server and I would like to know how to return the exact columns of tables in UDFs.

The situation is like this : I am going to query a table and then return it again as table.

Example : 

create function test
var t
set t = select * from table where condition
return t;

How am I going to do it?

(question is only how to return the table with dynamic fields)

Hope you understand the question. Thanks in advance

UPDATE : my question is somehow like how do I create a table variable where its columns are dynamic.

Solution

  • User defined functions have several limitations (they are meant not to have side-effects - cannot anything in the database - etc.). For your particular case, the following prohibit you to do your work:

    • dynamic SQL cannot be used
    • temporary tables are not allowed

    So, a function can only work if it knows what is the structure of the result at compile time.

    I would consider a procedure to achieve what you need, since it allows more flexibility. However, this comes with the headache of sharing data between stored procedures.

    It is not specified in the question, but ideally, this dynamic processing should stay in a high level language like C# or Java that have more support for dynamic structures (e.g. LINQ or Dynamic LINQ in C#)