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.
This question is related to
sql-server
sql-server-2008
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.
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)<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
Source: Stackoverflow.com