Assign function result to a table variable

Question

The SQL Server (2000/2005) function gets 'table name' and 'field name' as a parameter and returns results from a dynamic query within the function. The results should be assigned to a Table variable which will be used further in a Stored procedure. How to achieve this?

I am getting error: "Only functions and extended stored procedures can be executed from within a function."

Code:

Declare @Data as table (FieldValue varchar(100)) insert into @Data select * from MyFunction ('Person.Address','AddressID')

-- Function

Alter function MyFunction ( @TableName varchar(100), @FieldName varchar(100) ) returns @GetData table ( FieldValue varchar(100)

) as begin Declare @SQL varchar(250) Set @SQL = 'Select '+@FieldName+ ' from '+ @TableName Exec sp_executesql @SQL return end

Answer 1

I'm not sure how this works with functions, but if you have a Stored Procedure that returns a resultset, you can insert that into a table variable using INSERT EXEC statements.

INSERT @TableVariable
EXEC spYourProcedure

As long as the fields match that will work. Otherwise you can use:

INSERT @TableVariable (FieldInSp1, FieldInSp2)
EXEC spYourProcedure

This way you can pass data between stored procedures. See this page on INSERT EXEC Statements for some extra information.

Answer 2

You can't use "exec" in a user defined function. UDFs must be side effect free.