将函数结果分配给表变量 [英] Assign function result to a table variable

问题描述

SQL Server (2000/2005) 函数获取表名和字段名作为参数，并从函数内的动态查询返回结果.结果应分配给一个 Table 变量，该变量将在存储过程中进一步使用.如何实现这一目标?

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

推荐答案

我不确定这对函数是如何工作的，但是如果您有一个返回结果集的存储过程，您可以使用 INSERT 将其插入到表变量中EXEC 语句.

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

这样你就可以在存储过程之间传递数据.有关更多信息，请参阅INSERT EXEC 语句上的此页面.

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

这篇关于将函数结果分配给表变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！