Function that returns table

Hello,
Is there any way to write a function where I can write some code and at the end of the code return a entire table as parameter?

This function will return a variable temp table...

GO

SETANSI_NULLSOFF

GO

SETQUOTED_IDENTIFIERON

GO

CREATEFUNCTION [dbo].[UDF_ADMIN_GetNewSortList]

(

@.tText TEXT,

@.iSortStart INT

)

RETURNS @.NewList TABLE

(

iNewSortOrder INT

)

AS

BEGIN

Declare @.tmpList TABLE

(

iOldSortOrder INT

)

INSERTINTO @.tmpList(iOldSortOrder)

SELECT

[cValue]

FROM

[CPDB].[dbo].[udfCharListToTable]

(

@.tText

,','

)

DECLARE @.iCount INT

SELECT @.iCount =Count(*)

FROM @.tmpList

DECLARE @.iStart INT

SET @.iStart = 1

WHILE @.iStart <= @.iCount

BEGIN

INSERTINTO @.NewList(iNewSortOrder)

VALUES(@.iSortStart + @.iStart)

Set @.iStart = @.iStart + 1

END

RETURN

END

|||Yes, but a table with one column. I want to return a table that has 0 or more columns (depends on the application). In your example the function returns a table with only one column (iNewSortOrder).
Now I have:
CREATE PROCEDURE dbo.GetConfiguration
(
@.type varchar(MAX)
)
AS
IF @.type = 'Tank' SELECT * FROM Tank();
That stored procedure returns a table. Now I want to use this table in a Function... eg:
CREATE FUNCTION dbo.Configuration
(
@.type varchar(max)
)
RETURNS TABLE
AS
RETURN SELECT * FROM GetConfiguration @.type
Obviously that doesn't work. Thanks for any ideas!!!
|||"That does not work" answers are welcome, too (so I know that there's no way) ;-)
|||

Looking at the options for Create Function there are two choices that return a table. The first is an in-line function. In this case you do not have to define the columns of the table, but you can only have a single select statement. So, that won't work.

It is possible to have a general multi-statement function that returns a table, but that requires that the columns be known in advance. Assuming that there was value in having a function that had constant columns, but came from different tables -- maybe you have a number of name-value-pair type tables -- you now have another problem of how to populate the table variable. The most general approach would be to use dynamic SQL, but you cannot populate table variables with dynamic SQL. You could use a series of IF statements, but now you are pretty far away from your original goal.

In general, this is not the correct approach to take.

Have you looked into using Dynamic SQL to solve your problem?