如何从tsql的execute获取结果集? [英] How to get the result sets from tsql's execute?

问题描述

我尝试远程查询此SO 答案中提供的表值函数.

I try to remotely query table-valued function as it is offered in this SO answer.

但是我偶然发现了如何获取返回的结果集，以便在sql代码中进一步使用它们……

But I stumbled over how to get the returned result sets to further work with them in sql code...

SQL Server不支持远程调用UDF，并且openquery不能具有参数-仅静态字符串.

Calling UDF remotely is not supported by SQL Server and openquery cannot have parameters - only static string.

declare @query nvarchar(max) = 'select * into #workingDays from openquery(LNKDSRV, ''select * from DB.dbo.fxn_getWorkingDays('''''
    + cast(@date1 as nvarchar(max))
    + ''''',''''' 
    + cast(@date2 as nvarchar(max))
    + ''''')'')';
exec sys.sp_executesql @query;

稍后查询#workinDays时，出现错误无效的对象名称" .

When #workinDays is later queried there is a error 'invalid object name'.

推荐答案

您必须在sp_executesql之前定义表以使其在会话中可用:

You have to define your table before sp_executesql to be available in the session:

Create table #tbl  
declare @query nvarchar(max) = 'insert into #tbl select * from....
exec sys.sp_executesql @query
select * from #tbl

另一种选择是使用全局临时表##tbl

Another option is to use global temp table ##tbl

这篇关于如何从tsql的execute获取结果集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！