Assign function result to a table variable

Question

The SQL Server (2000/2005) function gets the table name and the field name as parameters 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."

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

-1. Don't use dynamic Sql this way. Refer to sommarskog: http://www.sommarskog.se/dynamic_sql.html — Amy B, Jan 21 '09 at 06:16
+1. @David: He is asking, no need to downvote because he is wrong. — Eduardo Molteni, Jan 21 '09 at 18:25

score 8 · Answer 1 · answered Dec 12 '16 at 16:46

8

just to close the loop...

here is the syntax for calling the function and putting those results in a table variable

small build on @simons solution

this ran on sql2012 and sql2014.

[ dont forget to close off the table statement. Easy enough to do if you have the table all on a single line. ]

declare @t table(field1  nvarchar(100) )

insert @t select * from dbo.Cool_1Field_Function( 'parm1' ,'parm2')
select * from @t

answered Dec 12 '16 at 16:46

greg

1,673
1
17
30

1

This should be the correct answer. It's the only one that uses a function and not a procedure. – Storm Muller Apr 09 '18 at 07:38
1

too bad you cannot do: `declare @t table(field1 nvarchar(100) ) = dbo.Cool_1Field_Function( 'parm1' ,'parm2')` – boggy Aug 18 '20 at 01:02
@costa, add it to the list of 'stuff that would be cool if....'. I have a reasonable list for sql :-) – greg Aug 21 '20 at 23:53

score 1 · Accepted Answer · answered Jan 21 '09 at 05:52

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.

score 1 · Answer 3 · answered Jan 21 '09 at 10:24

1

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

answered Jan 21 '09 at 10:24

Dan Sydner

428
3
12

Assign function result to a table variable

3 Answers3

Linked