如何等待查询返回结果?
问题描述:
是否有可能让查询等到它有结果而不是立即返回空结果集。例如:如何等待查询返回结果?
SELECT Column1 FROM Table1
如果Table1
是空的,查询将被空的结果集返回。但是,我希望它不会返回,但至少要等到一行可用,最好是某种类型的超时。如果可能的话,我宁愿这样做,而不涉及Service Broker。
澄清:
CLR在服务器上启用,但呼叫从通过SQLAPI ++/ODBC独立于平台的C++程序来。所以,没有C#/ .net技巧是可能的。目标是调用一个存储过程,指定一个超时值,直到数据可用(并由存储过程返回)或指定的超时过期才返回。
例如:
EXEC GetData @Timeout=2000 -- Wait for upto 5000 milliseconds for a result set to be
-- available
答
丑陋,但是有效。只要正在执行的查询是低成本的,例如等待行出现在空表中,这不应该是太多的资源猪。
declare @Timeout as Int = 5000 -- Milliseconds.
-- Start and end times.
declare @Now as DateTime = GetDate()
declare @TimeoutExpiration as DateTime = DateAdd(ms, @Timeout, @Now)
-- Set the delay interval to the smaller of @Timeout/10 or 1 second.
declare @DelayInterval as Time = DateAdd(ms, @Timeout/10, 0)
if @DelayInterval > '00:00:01.000'
set @DelayInterval = '00:00:01.000'
declare @WaitForDelay as VarChar(12) = Cast(@DelayInterval as VarChar(12)) -- WaitFor insists on a truncated string for the delay.
select @Timeout as 'Timeout (ms)', @Now as 'Now', @TimeoutExpiration as 'TimeoutExpiration', @DelayInterval as 'DelayInterval'
declare @Result as Table (Foo Int) -- Modify to match the schema of your expected results.
-- Execute the query in a loop until either a result is returned or the timeout expires.
declare @RowCount as Int = 0
declare @Iterations as Int = 0
while @TimeoutExpiration >= GetDate() and @RowCount = 0
begin
-- Randomly decide to insert a row into the results. (Replace with your query.)
insert into @Result
select 42 as Foo
where Rand() > 0.8
-- Handle the query result.
set @RowCount = @@RowCount
if @RowCount = 0
waitfor delay @WaitForDelay
set @Iterations = @Iterations + 1
end
-- Return the result.
select @Iterations as 'Iterations' -- Just for demonstration purposes.
select *
from @Result
clr enabled可以用吗? (c#代码...)? –
clr在服务器上启用,可以在存储的proc调用中使用。 (调用代码是与平台无关的代码,它使用SQLAPI ++库通过ODBC访问数据库,因此在呼叫方面没有太大的灵活性) –
听起来像是服务代理和“WAITFOR”的设计用途。为什么不愿意使用它? –