On 3?19?, ??2?00?, "mang...@gmail.com" <mang....@gmail.com> wrote:
> Hi,
>
> When I set WHILE @@FETCH_STATUS = 0, I still have the same result.
>
> My understanding of <> -1 is that it would return a blank or a result
> (and there are results in this table set, when I nest a PRINT @work
> within the Cursor loop it returns the correct data, as a single line,
> but there is something magical about conticating it with iself
> (@string) that makes it flip out..
>
> On Mar 18, 10:43 pm, "xyb" <xiangyua...@gmail.com> wrote:
>
>
>
> > On 3?19?, ??1?25?, mang...@gmail.com wrote:
>
> > > I am having a problem with returning a string of values from the
> > > table, this will be part of a scalar function but here is where the
> > > code is jamming up.
>
> > > Code:
> > > DECLARE @string as nvarchar(200), @work nvarchar(200), @omBranchID
> > > int
> > > set @omBranchID = 11601
> > > declare MyCursor CURSOR FOR SELECT dbo.omStatus.omStatusAlphaShort
> > > FROM dbo.omBranchStatus INNER JOIN
> > > dbo.omStatus ON
> > > dbo.omBranchStatus.omStatusID = dbo.omStatus.omStatusID
> > > WHERE dbo.omBranchStatus.omBranchID = @omBranchID AND
> > > omBranchStatus.omBranchStatusComplete = 0
> > > Order By omStatus.omStatusRank
>
> > > OPEN MyCursor
> > > FETCH MyCursor INTO @work
> > > WHILE @@FETCH_STATUS <> -1
> > > BEGIN
>
> > > set @string = @string + @work
>
> > > FETCH MyCursor INTO @work
> > > END
> > > DEALLOCATE MyCursor
>
> > > PRINT @string
>
> > > What is the solution to this and what is the problem called (so I can
> > > learn about it)
>
> > e :) i and yourself have not read code carefully.
> > this line:
> > while @@fetch_status <> -1 --error here
> > begin
> > ...
> > end
>
> > 0 good
> > -1error or record not in the set
> > -2 record not in the set
> > look at BOL for more info.- ??????? -
>
> - ??????? -
my test code below:
create function test(@vinput varchar(10))
returns varchar(1000)
as
begin
declare @string varchar(200)
declare @tempstr varchar(200)
select @string = ''
select @tempstr = ''
declare myCur cursor
for
select [id] from sysobjects where [id] < 100
open myCur
fetch myCur into @tempstr
while @@fetch_status = 0
begin
select @string = @string + @tempstr + ','
fetch myCur into @tempstr
end
deallocate myCur
return @string
end
go
select dbo.test('test')
drop function dbo.test