函数在SQL SERVER 2008中使用EXEC返回表 [英] Function returns table using EXEC in SQL SERVER 2008

问题描述

大家好，



我有一个已发布的服务器。



我写的是使用Exec查询返回表的函数。



但不幸的是我收到错误

Hi Guys,

I have a published server.

Where i'm write a function which returns a table using Exec query.

But Unfortunately i'm getting error

Msg 443, Level 16, State 14, Procedure Fn_GetDailyRentalFullDataForView, Line 15
Invalid use of a side-effecting operator 'INSERT EXEC' within a function.

Msg 444, Level 16, State 2, Procedure Fn_GetDailyRentalFullDataForView, Line 18
Select statements included within a function cannot return data to a client.

Msg 455, Level 16, State 2, Procedure Fn_GetDailyRentalFullDataForView, Line 18
The last statement included within a function must be a return statement.

我尝试过：

What I have tried:

Create Function Fn_GetDailyRentalFullDataForView()
Returns  @t table(
					[VehicleType] [int] NULL,
					[vt] [nvarchar](50) NULL,
					[TruckTotal] [nvarchar](50) NULL,
					[Description] [nvarchar](max) NULL,
					[Driver] [nvarchar](50) NULL,
					[Location] [nvarchar](250) NULL,
					[Department] [nvarchar](50) NULL,
					[JobDate] [date] NULL
				)

as
begin
insert into @t				
exec Sp_GetDailyRentalFullDataForView

select * from @t
end

这是我的功能。



请让我知道，我哪里出错。



谢谢

This is my Function.

Please let me know, where i'm getting wrong.

Thanks

推荐答案

这个合作de是非法的，因为您无法从函数执行存储过程（阅读文档 [ ^ ]）：

This code is illegal as you cannot execute stored procedure from a function (read the documentation[^]):

insert into @t				
exec Sp_GetDailyRentalFullDataForView

您还必须通过return返回结果而不是选择：

You must also return results via return not select:

Create Function Fn_GetDailyRentalFullDataForView()
Returns  @t table(...)
as
begin
-- populate @t 
return
end

这篇关于函数在SQL SERVER 2008中使用EXEC返回表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！