Assigning a function result to a table variable

Question

The SQL Server function (2000/2005) takes the table name and field name as parameters and returns the results of the dynamic query inside the function. The results should be assigned to the Table variable, which will be used later in the stored procedure. How to achieve this?

I get an error: "Only functions and extended stored procedures can be executed inside a function."

 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 result set, you can insert this into a table variable using INSERT EXEC statements.

 INSERT @TableVariable EXEC spYourProcedure 

So far, the fields that will work are the same. Otherwise, you can use:

 INSERT @TableVariable (FieldInSp1, FieldInSp2) EXEC spYourProcedure 

This way you can transfer data between stored procedures. See the INSERT EXEC Statementments page for more information.

Answer 2

You cannot use "exec" in a custom function. UDF should be free of side effects.

Answer 3

to close the loop ...

Here is the syntax for calling a function and putting these results in a table variable

small build on @simons solution

This was done on sql2012 and sql2014.

[do not forget to close the table statement. Easy enough to do if you have a table on one line. ]

 declare @t table(field1 nvarchar(100) ) insert @t select * from dbo.Cool_1Field_Function( 'parm1' ,'parm2') select * from @t