A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations

Question

What is wrong with this statement?

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

Just to explain the answer (after getting where the error was caused), I simply removed @adjustedLow from the second select statement.

Answer 1

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:

The problem is here:

select      top 1 @low = low     , @day = day     , @adjustedLow  -- causes error! --select high from      securityquote sq ... 

Detailed explanation and workaround: SQL Server Error Messages - Msg 141 - A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Answer 2

You cannot use a select statement that assigns values to variables to also return data to the user The below code will work fine, because i have declared 1 local variable and that variable is used in select statement.

 Begin     DECLARE @name nvarchar(max)     select @name=PolicyHolderName from Table     select @name     END 

The below code will throw error "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations" Because we are retriving data(PolicyHolderAddress) from table, but error says data-retrieval operation is not allowed when you use some local variable as part of select statement.

 Begin     DECLARE @name nvarchar(max)     select         @name = PolicyHolderName,        PolicyHolderAddress      from Table  END 

The the above code can be corrected like below,

Begin     DECLARE @name nvarchar(max)     DECLARE @address varchar(100)     select         @name = PolicyHolderName,        @address = PolicyHolderAddress      from Table END 

So either remove the data-retrieval operation or add extra local variable. This will resolve the error.