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
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.