Cursor problem

Hello
When I run this cursor I get the value 01042005 inserting into my table
twice when the cursor gets to the end of the recordset. How can i store just
one instance of this value? I thought coding WHILE @.@.FETCH_STATUS = 0 would
stop it from looping again when it got to the end of the recordset.
DECLARE @.name varchar(19)
DECLARE trans_names CURSOR FOR
select name from sysobjects where name like '%TRANS%' and
substring(name,12,6)= '042005'
order by convert(int,substring(name,14,4)) desc,substring(name,12,2)
desc,substring(name,10,2) desc
OPEN trans_names
FETCH NEXT FROM trans_names INTO @.name
INSERT #TABLES VALUES (blah blah)
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM trans_names INTO @.name
INSERT #TABLES VALUES (blah blah)
END
CLOSE trans_names
DEALLOCATE trans_names
GOWhy don't you use:
INSERT INTO #TABLES (<column list> )
select name from sysobjects where name like '%TRANS%' and
substring(name,12,6)= '042005'
But anyway, if you really need a cursor, I find writing the loop in the
following way the most convenient:
DECLARE <cursor name> CURSOR FAST_FORWARD FOR <select statement>
OPEN <cursor name>
WHILE 1 = 1
BEGIN
FETCH NEXT FROM <cursor name> INTO <variables>
IF @.@.FETCHSTATUS <> 0 BREAK -- ends loop
-- do stuff
END
Jacco Schalkwijk
SQL Server MVP
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:65D7FB7A-860C-4CDF-8419-4FC8ED96F93A@.microsoft.com...
> Hello
> When I run this cursor I get the value 01042005 inserting into my table
> twice when the cursor gets to the end of the recordset. How can i store
> just
> one instance of this value? I thought coding WHILE @.@.FETCH_STATUS = 0
> would
> stop it from looping again when it got to the end of the recordset.
> DECLARE @.name varchar(19)
> DECLARE trans_names CURSOR FOR
> select name from sysobjects where name like '%TRANS%' and
> substring(name,12,6)= '042005'
> order by convert(int,substring(name,14,4)) desc,substring(name,12,2)
> desc,substring(name,10,2) desc
>
> OPEN trans_names
> FETCH NEXT FROM trans_names INTO @.name
> INSERT #TABLES VALUES (blah blah)
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM trans_names INTO @.name
> INSERT #TABLES VALUES (blah blah)
> END
> CLOSE trans_names
> DEALLOCATE trans_names
> GO|||I think the problem is that the insert executes after the fetch next, but
before the @.@.Fetch_status is evaluated. Try this as an alternative
FETCH NEXT FROM trans_names INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT #TABLES VALUES (blah blah)
FETCH NEXT FROM trans_names INTO @.name
END
Gail Shaw (MCSD)
http://gail.rucus.net/
"Steve" wrote:

> Hello
> When I run this cursor I get the value 01042005 inserting into my table
> twice when the cursor gets to the end of the recordset. How can i store ju
st
> one instance of this value? I thought coding WHILE @.@.FETCH_STATUS = 0 woul
d
> stop it from looping again when it got to the end of the recordset.
> DECLARE @.name varchar(19)
> DECLARE trans_names CURSOR FOR
> select name from sysobjects where name like '%TRANS%' and
> substring(name,12,6)= '042005'
> order by convert(int,substring(name,14,4)) desc,substring(name,12,2)
> desc,substring(name,10,2) desc
>
> OPEN trans_names
> FETCH NEXT FROM trans_names INTO @.name
> INSERT #TABLES VALUES (blah blah)
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM trans_names INTO @.name
> INSERT #TABLES VALUES (blah blah)
> END
> CLOSE trans_names
> DEALLOCATE trans_names
> GO|||Thanku very much. It's working now.
"GilaMonster" wrote:
> I think the problem is that the insert executes after the fetch next, but
> before the @.@.Fetch_status is evaluated. Try this as an alternative
> FETCH NEXT FROM trans_names INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> INSERT #TABLES VALUES (blah blah)
> FETCH NEXT FROM trans_names INTO @.name
> END
>
> --
> Gail Shaw (MCSD)
> http://gail.rucus.net/
>
> "Steve" wrote:
>