| Groups | Blog | Home |
sql server programming : What is wrong with this String operation
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)
[quoted text, click to view]
On 3=D4=C219=C8=D5, =CF=C2=CE=E71=CA=B125=B7=D6, 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 =3D 11601 > declare MyCursor CURSOR FOR SELECT dbo.omStatus.omStatusAlphaShort > FROM dbo.omBranchStatus INNER JOIN > dbo.omStatus ON > dbo.omBranchStatus.omStatusID =3D dbo.omStatus.omStatusID > WHERE dbo.omBranchStatus.omBranchID =3D @omBranch= ID AND > omBranchStatus.omBranchStatusComplete =3D 0 > Order By omStatus.omStatusRank > > OPEN MyCursor > FETCH MyCursor INTO @work > WHILE @@FETCH_STATUS <> -1 > BEGIN > > set @string =3D @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) em... your problem are clearly described. set @string =3D @string + @work this line i can guess @string should to be declare as varchar(8000),or the date lenght will not enough.
Alas,
That does not fix it either. I am not getting a message or anything, just blankness. I even tried getting sneaky and passes just the column ID number and did a nested select statement (incredibly inefficient) [quoted text, click to view] On Mar 18, 10:31 pm, "xyb" <xiangyua...@gmail.com> wrote: > On 3=E6=9C=8819=E6=97=A5, =E4=B8=8B=E5=8D=881=E6=97=B625=E5=88=86, 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), @omBranc= hID > > int > > set @omBranchID =3D 11601 > > declare MyCursor CURSOR FOR SELECT dbo.omStatus.omStatusAlphaSh= ort > > FROM dbo.omBranchStatus INNER JOIN > > dbo.omStatus ON > > dbo.omBranchStatus.omStatusID =3D dbo.omStatus.omStatusID > > WHERE dbo.omBranchStatus.omBranchID =3D @omBran= chID AND > > omBranchStatus.omBranchStatusComplete =3D 0 > > Order By omStatus.omStatusRank > > > OPEN MyCursor > > FETCH MyCursor INTO @work > > WHILE @@FETCH_STATUS <> -1 > > BEGIN > > > set @string =3D @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) > > em... your problem are clearly described. > set @string =3D @string + @work > this line i can guess @string should to be declare as varchar(8000),or > the date lenght will not enough.
[quoted text, click to view]
On 3=D4=C219=C8=D5, =CF=C2=CE=E71=CA=B125=B7=D6, 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 =3D 11601 > declare MyCursor CURSOR FOR SELECT dbo.omStatus.omStatusAlphaShort > FROM dbo.omBranchStatus INNER JOIN > dbo.omStatus ON > dbo.omBranchStatus.omStatusID =3D dbo.omStatus.omStatusID > WHERE dbo.omBranchStatus.omBranchID =3D @omBranch= ID AND > omBranchStatus.omBranchStatusComplete =3D 0 > Order By omStatus.omStatusRank > > OPEN MyCursor > FETCH MyCursor INTO @work > WHILE @@FETCH_STATUS <> -1 > BEGIN > > set @string =3D @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 ..=2E. end 0 good -1error or record not in the set -2 record not in the set look at BOL for more info.
Hi, When I set WHILE @@FETCH_STATUS =3D 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.. [quoted text, click to view] On Mar 18, 10:43 pm, "xyb" <xiangyua...@gmail.com> wrote: > On 3=E6=9C=8819=E6=97=A5, =E4=B8=8B=E5=8D=881=E6=97=B625=E5=88=86, 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), @omBranc= hID > > int > > set @omBranchID =3D 11601 > > declare MyCursor CURSOR FOR SELECT dbo.omStatus.omStatusAlphaSh= ort > > FROM dbo.omBranchStatus INNER JOIN > > dbo.omStatus ON > > dbo.omBranchStatus.omStatusID =3D dbo.omStatus.omStatusID > > WHERE dbo.omBranchStatus.omBranchID =3D @omBran= chID AND > > omBranchStatus.omBranchStatusComplete =3D 0 > > Order By omStatus.omStatusRank > > > OPEN MyCursor > > FETCH MyCursor INTO @work > > WHILE @@FETCH_STATUS <> -1 > > BEGIN > > > set @string =3D @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.
On 3=D4=C219=C8=D5, =CF=C2=CE=E72=CA=B100=B7=D6, "mang...@gmail.com" <mang.=
[quoted text, click to view] ..=2E@gmail.com> wrote: > Hi, > > When I set WHILE @@FETCH_STATUS =3D 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=D4=C219=C8=D5, =CF=C2=CE=E71=CA=B125=B7=D6, mang...@gmail.com wrot= e: > > > > 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), @omBra= nchID > > > int > > > set @omBranchID =3D 11601 > > > declare MyCursor CURSOR FOR SELECT dbo.omStatus.omStatusAlpha= Short > > > FROM dbo.omBranchStatus INNER JOIN > > > dbo.omStatus ON > > > dbo.omBranchStatus.omStatusID =3D dbo.omStatus.omStatusID > > > WHERE dbo.omBranchStatus.omBranchID =3D @omBr= anchID AND > > > omBranchStatus.omBranchStatusComplete =3D 0 > > > Order By omStatus.omStatusRank > > > > OPEN MyCursor > > > FETCH MyCursor INTO @work > > > WHILE @@FETCH_STATUS <> -1 > > > BEGIN > > > > set @string =3D @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.- =D2=FE=B2=D8=B1=BB=D2=FD=D3=C3=CE=C4=D7=D6 - > > - =CF=D4=CA=BE=D2=FD=D3=C3=B5=C4=CE=C4=D7=D6 - 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 =3D '' select @tempstr =3D '' declare myCur cursor for select [id] from sysobjects where [id] < 100 open myCur fetch myCur into @tempstr while @@fetch_status =3D 0 begin select @string =3D @string + @tempstr + ',' fetch myCur into @tempstr end deallocate myCur return @string end go select dbo.test('test') drop function dbo.test
Hi
Make sure that @work variable was not set to NULL. [quoted text, click to view] <manganb@gmail.com> wrote in message news:1174281945.121735.98210@n76g2000hsh.googlegroups.com... >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) >
How exactly is it jamming up?
Try putting SET @string = '' , prior to the DECLARE CURSOR starting , and reply with what result you get? Jack Vamvas ___________________________________ Advertise your IT vacancies for free at - http://www.ITjobfeed.com [quoted text, click to view] <manganb@gmail.com> wrote in message news:1174281945.121735.98210@n76g2000hsh.googlegroups.com... >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) >
Thank you Jack and Uri,
Both your advice fixed it. Why is it that I had to set the string to ''? and there was a null result in the set, and so ISNULL took care of that wee issue. -Brian Below is the working code: DECLARE @string varchar(8000), @work varchar(10), @omBranchID int set @omBranchID = 17674 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 set @string = '' OPEN MyCursor FETCH MyCursor INTO @work WHILE @@FETCH_STATUS = 0 BEGIN set @string = @string + ' ' + ISNULL(@work,'') FETCH MyCursor INTO @work END DEALLOCATE MyCursor PRINT @string [quoted text, click to view] On Mar 19, 1:46 am, "Jack Vamvas" <DEL_TO_RE...@del.com> wrote: > How exactly is it jamming up? > Try putting SET @string = '' , prior to the DECLARE CURSOR starting , > and reply with what result you get? > > Jack Vamvas > ___________________________________ > Advertise your IT vacancies for free at -http://www.ITjobfeed.com > > <mang...@gmail.com> wrote in message > > news:1174281945.121735.98210@n76g2000hsh.googlegroups.com... > > >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)
[quoted text, click to view]
On 19 Mar 2007 09:18:32 -0700, manganb@gmail.com wrote: >Why is it that I had to set the string to ''? Hi Brian, That's because according to the ANSI standard for SQL, any expression that includes at least one NULL argument should result in NULL (unless explicitly noted otherwise, which is only the case for very few expressions). The reason behind this, is that NULL signifies that the data is missing from the database, not that it doesn't exist at all. It might exist, it is just not known to the database. So if you concatenate something to an unknown string, the result surely can only be another unknown string. -- Hugo Kornelis, SQL Server MVP
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |