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  gt   convert varchar 10    start  111  and convert varchar 10    end  111   gt   isq day                             and low    -1                                as x  select      top 1  low   low        day   day        adjustedLow --select high from      securityquote sq where      day  gt   convert varchar 10    start  111  and convert varchar 10    end  111   gt   day     and securityid    securityid and low    -1 order by low asc      set  results     lt results type  debug min  gt       set  results    results     lt periodStart gt     coalesce cast  start as varchar 20     NULL       lt  periodStart gt        set  results    results     lt periodEnd gt     coalesce cast  end  as varchar 20     NULL       lt  periodEnd gt        set  results    results     lt securityID gt     coalesce cast  securityID as varchar 10     NULL       lt  securityID gt       set  results    results     lt periodMin gt     coalesce cast  low as varchar 10     -11111       lt  periodMin gt       set  results    results     lt coraxAdjustedPeriodMin gt     coalesce cast  adjustedLow as varchar 10     -11111       lt  coraxAdjustedPeriodMin gt       set  results    results     lt dayMinOcurred gt     coalesce  day   NULL       lt  dayMinOcurred gt       set  results    results     lt  results gt    return  results   Just to explain the answer  after getting where the error was caused   I simply removed  adjustedLow from the second select statement

User · Accepted Answer

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.

User · Answer

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

User · Answer

declare  cur cursor declare  idx int        declare  Approval No varchar 50    declare  ReqNo varchar 100  declare  M Id  varchar 100  declare  Mail ID varchar 100  declare  temp  table   val varchar 100    declare  temp2  table   appno varchar 100   mailid varchar 100   userod varchar 100          declare  slice varchar 8000             declare  String varchar 100      --set  String    1200096 1200095 1200094 1200093 1200092 1200092  set  String    20131        select  idx   1                if len  String  lt 1 or  String is null  return             while  idx   0            begin                set  idx   charindex      String                 if  idx  0                    set  slice   left  String  idx - 1                 else                    set  slice    String              --select  slice                    insert into  temp values  slice          set  String   right  String len  String  -  idx                 if len  String    0 break       end     -- select distinct val  from  temp   SET  cur   CURSOR FOR select distinct val  from  temp   --open cursor     OPEN  cur     --fetchng id into variable     FETCH NEXT         FROM  cur into  Approval No         --     --loop still the end          while   FETCH STATUS   0       BEGIN      select distinct Approval Sr No  as asd   ReqNo Approval Sr No  M Id AM ID  Mail ID Mail ID from WFMS PRAO WFMS USERMASTER where  WFMS PRAO AM ID WFMS USERMASTER User ID and Approval Sr No  Approval No     insert into  temp2 values  ReqNo  M Id  Mail ID     FETCH NEXT           FROM  cur into  Approval No      end       --close cursor         CLOSE  cur      select   from  tem

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

Examples related to sql-server

Examples related to sql-server-2008