Can a dynamic table be returned by a function or stored procedure in SQL Server?

I would like to call a stored procedure or user-defined function that returns a dynamic table that is created via a pivot expression. I don't know the number of columns up fr开发者_StackOverflow中文版ont.

Is this possible? (I am not interested in temporary tables)

You can do that via stored procedure as it can return any kind of table, question is what are you trying to achieve and what will you do with data that you have no idea about?

This cannot be done with functions (as the returned table structure must be pre-defined), but it can be done with a stored proceed. Some psuedo-code:

CREATE PROCEDURE Foo

As

DECLARE @Command

SET @Command = 'SELECT * from MyTable'

-- For debugging, work in an optional PRINT @Command statement
EXECUTE (@Command)

RETURN 0

When you run stored procedure Foo, it builds your query as a string in @Command, and then dynamically executes it without knowing anything about what is being queried or returned, and the data set returned by that EXECUTE statement is "passed back" to the process that called the procedures.

Build your query with care, this stuff can be really hard to debug. Depending on your implementation, it might be a source of SQL injection attacks (remember, the stored procedure really doesn't know what that dynamic query is going to do). For quick stuff, EXECUTE() works fine, but for safer and more useful (if elaborate) solutions, look into sp_ExecuteSQL.

Yes, you can do this from a Stored Procedure, but not from a user-defined Function. It is worth looking into the Table Value Function, I believe you can also return a dynamic table from there, but I have not used that myself.