为变量赋值的 SELECT 语句不得与数据检索操作结合使用 [英] A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations
本文介绍了为变量赋值的 SELECT 语句不得与数据检索操作结合使用的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这个说法有什么问题?
ALTER Function [InvestmentReturn].[getSecurityMinLowForPeriod](@securityid int,
@start datetime,
@end datetime)
returns xml
begin
declare @results varchar(500)
declare @low int
declare @adjustedLow int
declare @day varchar(10)
if @end is null
begin
set @end = getdate()
end
set @adjustedLow = (select min(adjLow)
from (
select Low * [InvestmentReturn].[fn_getCorporateActionSplitFactor](isq.securityid, @start, convert(varchar,day, 111)) as adjLow
from
securityquote isq
where isq.securityid = @securityid and isq.day >= convert(varchar(10), @start, 111) and convert(varchar(10), @end, 111) >= isq.day
and low != -1
) as x)
select
top 1 @low = low
, @day = day
, @adjustedLow
--select high
from
securityquote sq
where
day >= convert(varchar(10), @start, 111) and convert(varchar(10), @end, 111) >= day
and securityid = @securityid and low != -1
order by low asc
set @results= '<results type="debug_min">'
set @results = @results + '<periodStart>' + coalesce(cast(@start as varchar(20)), 'NULL') + '</periodStart>'
set @results = @results + '<periodEnd>' + coalesce(cast(@end as varchar(20)), 'NULL') + '</periodEnd>'
set @results = @results + '<securityID>' + coalesce(cast(@securityID as varchar(10)), 'NULL') + '</securityID>'
set @results = @results + '<periodMin>' + coalesce(cast(@low as varchar(10)), '-11111') + '</periodMin>'
set @results = @results + '<coraxAdjustedPeriodMin>' + coalesce(cast(@adjustedLow as varchar(10)), '-11111') + '</coraxAdjustedPeriodMin>'
set @results = @results + '<dayMinOcurred>' + coalesce(@day, 'NULL') + '</dayMinOcurred>'
set @results = @results + '</results>'
return @results
只是为了解释答案(在了解导致错误的位置之后),我只是从第二个 select 语句中删除了 @adjustedLow.
Just to explain the answer (after getting where the error was caused), I simply removed @adjustedLow from the second select statement.
推荐答案
来自 SELECT 语句的列值被分配到 @low
和 @day
局部变量;@adjustedLow
值未分配给任何变量并导致问题:
Column values from the SELECT statement are assigned into @low
and @day
local variables; the @adjustedLow
value is not assigned into any variable and it causes the problem:
问题出在这里:
select
top 1 @low = low
, @day = day
, @adjustedLow -- causes error!
--select high
from
securityquote sq
...
详细说明和解决方法:SQL Server 错误消息 - Msg 141- 为变量赋值的 SELECT 语句不得与数据检索操作结合使用.
这篇关于为变量赋值的 SELECT 语句不得与数据检索操作结合使用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文