"Declare @var CURSOR FOR" works only in a stored procedure ?????

Do as the error message says, declare the cursor in one batch and use it
in another like this:

declare FNBcur CURSOR FOR
select * from comp_data
go

declare @FNBsetid int
open FNBcur
fetch FNBcur into @FNBsetid
while (@@sqlstatus = 0)
begin
insert into attr_config (id) values (@FNBsetid)
fetch FNBcur into @FNBsetid
end
close ColuList
deallocate cursor ColuList
go

Post by grille11
Hello everyone,
I am trying to use the cursor function in a simple script like this one
Declare FNBcur CURSOR FOR
select * from comp_data
Open FNBcur
BEGIN
-- this
END
CLOSE ColuList
DEALLOCATE cursor ColuList
This works in a stored procedure but when I run this in a sql interface
(sql advantage for instance) I have this error
Error: Number (7344) Severity (15) State (1) Server (server) DECLARE
CURSOR must be the only statement in a query batch.
Would there be a way to make this work in a simple sql scritp?
Would anyone have an idea on this?
Thanks in advance for your help

--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

Bill Weissborn

2005-02-24 22:52:34 UTC

Try this:

Declare FNBcur CURSOR FOR
select * from comp_data
Declare @FNBsetid int
Open FNBcur
Fetch FNBcur into @FNBsetid
...

Bill W

Chris Staff

2005-03-04 13:37:32 UTC

Instead of using a cursor, you can use a temp table like this and you can put it all in one script. The only problem, is that its a little more code. But it should be pretty explanatory.
Let me know what you think.

/*No Count set for Preference */

SET NOCOUNT ON

DECLARE @FNBsetid int

/*These variables are used to loop through temp table*/

DECLARE @i int

SELECT @i = 1

/*holds the max integer*/

DECLARE @max int

/*Create Temp table to hold contents of "cursor"*/

CREATE TABLE #FNBcur (id numeric(8,0) identity,FNBsetid int)

INSERT INTO #FNBcur(FNBsetid)

SELECT FNBsetid FROM comp_data

SELECT @max = Max(id) FROM #FNBcur

/*Begin Loop*/

WHILE ( @max >= @i )

BEGIN

INSERT INTO attr_config (id)

VALUES (@FNBsetid)

/*increment row counter*/

SELECT @i = @i +1

END

/*Drop temp Table*/

DROP TABLE #FNBcur

"grille11" <***@yahoo.com> wrote in message news:421dee5c$***@forums-2-dub...
Hello everyone,

I am trying to use the cursor function in a simple script like this one (not a stored procedure) :

Declare @FNBsetid int
Declare FNBcur CURSOR FOR
select * from comp_data
Open FNBcur
Fetch FNBcur into @FNBsetid

while (@@sqlstatus = 0)
BEGIN
-- this
insert into attr_config (id) values (@FNBsetid)
Fetch FNBcur into @FNBsetid
END
CLOSE ColuList
DEALLOCATE cursor ColuList

This works in a stored procedure but when I run this in a sql interface (sql advantage for instance) I have this error

Error: Number (7344) Severity (15) State (1) Server (server) DECLARE CURSOR must be the only statement in a query batch.

Would there be a way to make this work in a simple sql scritp?
Would anyone have an idea on this?

Thanks in advance for your help

Iann

2005-03-04 15:15:37 UTC

I've never understood what it is about cursors that makes people want to
use more complex, and slower, constructs such as this temp table loop?

--ian

Post by Chris Staff
Instead of using a cursor, you can use a temp table like this and you
can put it all in one script. The only problem, is that its a little
more code. But it should be pretty explanatory.
Let me know what you think.
/*No Count set for Preference */
SET NOCOUNT ON
/*These variables are used to loop through temp table*/
/*holds the max integer*/
/*Create Temp table to hold contents of "cursor"*/
CREATE TABLE #FNBcur (id numeric(8,0) identity,FNBsetid int)
INSERT INTO #FNBcur(FNBsetid)
SELECT FNBsetid FROM comp_data
/*Begin Loop*/
BEGIN
INSERT INTO attr_config (id)
/*increment row counter*/
END
/*Drop temp Table*/
DROP TABLE #FNBcur
Hello everyone,
I am trying to use the cursor function in a simple script like this
Declare FNBcur CURSOR FOR
select * from comp_data
Open FNBcur
BEGIN
-- this
END
CLOSE ColuList
DEALLOCATE cursor ColuList
This works in a stored procedure but when I run this in a sql
interface (sql advantage for instance) I have this error
Error: Number (7344) Severity (15) State (1) Server (server)
DECLARE CURSOR must be the only statement in a query batch.
Would there be a way to make this work in a simple sql scritp?
Would anyone have an idea on this?
Thanks in advance for your help

Sherlock, Kevin

2005-03-04 17:26:49 UTC

And I must really be out of touch, because I can't figure out why one INSERT
statement wouldn't work in lieue of cursors or temp table solutions:

INSERT into attr_config(id)
SELECT FNBsetid from comp_data

Post by Iann
I've never understood what it is about cursors that makes people want to
use more complex, and slower, constructs such as this temp table loop?
--ian

Chris Staff

2005-03-04 17:51:54 UTC

I totally agree with you. I really don't like cursors. Oh and the
problem with using the cursor goes back to the original problem, "DECLARE
CURSOR must be the only statement in a query batch.". Has anyone done any
speed comparisons between using a Temp table loop and using a cursor?

Anyway the code I posted before was wrong. I forgot a little chunk of the
tsql. :)
This is what the script should look like.
/*No Count set for Preference */

SET NOCOUNT ON

DECLARE @FNBsetid int

/*These variables are used to loop through temp table*/

DECLARE @i int

SELECT @i = 1

/*holds the max integer*/

DECLARE @max int

/*Create Temp table to hold contents of "cursor"*/

CREATE TABLE #FNBcur (id numeric(8,0) identity,FNBsetid int)

INSERT INTO #FNBcur(FNBsetid)

SELECT FNBsetid FROM comp_data

SELECT @max = Max(id) FROM #FNBcur

/*Begin Loop*/

WHILE ( @max >= @i )

BEGIN

/*Forgot this code in first post*/

SELECT @FNBsetid = FNBsetid

FROM #FNBcur

WHERE id = @i

/*End of Missing code*/

INSERT INTO attr_config (id)

VALUES (@FNBsetid)

/*increment row counter*/

SELECT @i = @i +1

END

/*Drop temp Table*/

DROP TABLE #FNBcur

Post by Sherlock, Kevin
And I must really be out of touch, because I can't figure out why one INSERT
INSERT into attr_config(id)
SELECT FNBsetid from comp_data

Post by Iann
I've never understood what it is about cursors that makes people want to
use more complex, and slower, constructs such as this temp table loop?
--ian

Iann

2005-03-04 19:19:00 UTC

Chris,

In all cases I have tested, a cursor is faster than executing multiple
separate select statements against a temp table, as well as being easier
to code. The difference becomes extreme when the temp table becomes larger.

There are issues to be considered with cursor locking, but if you really
don't want to have cursor locks on a live table, then dump the result
set into a temp table and then cursor through the temp table. You can
also use this technique to avoid issues with updatable cursors when
there is no suitable unique index.

--ian

Post by Chris Staff
I totally agree with you. I really don't like cursors. Oh and the
problem with using the cursor goes back to the original problem, "DECLARE
CURSOR must be the only statement in a query batch.". Has anyone done any
speed comparisons between using a Temp table loop and using a cursor?
Anyway the code I posted before was wrong. I forgot a little chunk of the
tsql. :)
This is what the script should look like.
/*No Count set for Preference */
SET NOCOUNT ON
/*These variables are used to loop through temp table*/
/*holds the max integer*/
/*Create Temp table to hold contents of "cursor"*/
CREATE TABLE #FNBcur (id numeric(8,0) identity,FNBsetid int)
INSERT INTO #FNBcur(FNBsetid)
SELECT FNBsetid FROM comp_data
/*Begin Loop*/
BEGIN
/*Forgot this code in first post*/
FROM #FNBcur
/*End of Missing code*/
INSERT INTO attr_config (id)
/*increment row counter*/
END
/*Drop temp Table*/
DROP TABLE #FNBcur

Roberto Carlos Gandara P.

2008-01-28 21:39:50 UTC

MAKE IT AN STORED PROCEDURE.

On 4 Mar 2005 09:51:54 -0800,
in
sybase.public.ase.general,sybase.public.ase.administration,sybase.public.ase.nt,
sybase.public.ase.performance+tuning

Post by Sherlock, Kevin
And I must really be out of touch, because I can't figure out why one INSERT
INSERT into attr_config(id)
SELECT FNBsetid from comp_data

Post by Iann
I've never understood what it is about cursors that makes people want to
use more complex, and slower, constructs such as this temp table loop?
--ian

RCGP

unknown

2008-01-29 15:20:53 UTC

This is how to create cursor outside of Stored Procedure:

SQLAdvantage:
-------------
declare mycursor select col1 from mytable
go
declare @x
open mycursor
fetch mycursor into @x
while ...
begin
...
end
close mycursor
deallocate cursor mycur
go

Post by Roberto Carlos Gandara P.
MAKE IT AN STORED PROCEDURE.
On 4 Mar 2005 09:51:54 -0800,
in
sybase.public.ase.general,sybase.public.ase.administration
,sybase.public.ase.nt,
sybase.public.ase.performance+tuning Chris Staff
really don't like cursors. Oh and the >problem with
using the cursor goes back to the original problem,
"DECLARE >CURSOR must be the only statement in a query
batch.". Has anyone done any >speed comparisons between
using a Temp table loop and using a cursor? >

Post by Chris Staff
Anyway the code I posted before was wrong. I forgot a

little chunk of the >tsql. :)

Post by Chris Staff
This is what the script should look like.
/*No Count set for Preference */
SET NOCOUNT ON
/*These variables are used to loop through temp table*/
/*holds the max integer*/
/*Create Temp table to hold contents of "cursor"*/
CREATE TABLE #FNBcur (id numeric(8,0) identity,FNBsetid

int) >

Post by Chris Staff
INSERT INTO #FNBcur(FNBsetid)
SELECT FNBsetid FROM comp_data
/*Begin Loop*/
BEGIN
/*Forgot this code in first post*/
FROM #FNBcur
/*End of Missing code*/
INSERT INTO attr_config (id)
/*increment row counter*/
END
/*Drop temp Table*/
DROP TABLE #FNBcur

Post by Sherlock, Kevin
And I must really be out of touch, because I can't

figure out why one >> INSERT

Post by Sherlock, Kevin
statement wouldn't work in lieue of cursors or temp

table solutions: >>

Post by Sherlock, Kevin
INSERT into attr_config(id)
SELECT FNBsetid from comp_data

what it is about cursors that makes people want to >>> use
more complex, and slower, constructs such as this temp
table loop? >>>

--ian

Post by Chris Staff
Instead of using a cursor, you can use a temp table

like this and you >>> > can put it all in one script. The
only problem, is that its a little >>> > more code. But
it should be pretty explanatory. >>> > Let me know what
you think. >>> >

Post by Chris Staff
/*No Count set for Preference */
SET NOCOUNT ON
/*These variables are used to loop through temp

table*/ >>> >

Post by Chris Staff
/*holds the max integer*/
/*Create Temp table to hold contents of "cursor"*/
CREATE TABLE #FNBcur (id numeric(8,0) identity

,FNBsetid int) >>> >

Post by Chris Staff
INSERT INTO #FNBcur(FNBsetid)
SELECT FNBsetid FROM comp_data
/*Begin Loop*/
BEGIN
INSERT INTO attr_config (id)
/*increment row counter*/
END
/*Drop temp Table*/
DROP TABLE #FNBcur
Hello everyone,
I am trying to use the cursor function in a

simple script like this >>> > one (not a stored
procedure) : >>> >

Post by Chris Staff
Declare FNBcur CURSOR FOR
select * from comp_data
Open FNBcur
BEGIN
-- this
insert into attr_config (id) values
END
CLOSE ColuList
DEALLOCATE cursor ColuList
This works in a stored procedure but when I run

this in a sql >>> > interface (sql advantage for
instance) I have this error >>> >

Post by Chris Staff
Error: Number (7344) Severity (15) State (1)

Server (server) >>> > DECLARE CURSOR must be the only
statement in a query batch. >>> >

Post by Chris Staff
Would there be a way to make this work in a

simple sql scritp? >>> > Would anyone have an idea on
this? >>> >

Post by Chris Staff
Thanks in advance for your help

RCGP

Iann

2005-03-04 19:08:24 UTC

Sure, this one would best be done all in one go without loops of any
kind, but there are cases when looping through rows one by one is the
best solution for one reason or another. I assumed that there was more
to the situation here than the trivial loop given as an example.

--ian

Post by Sherlock, Kevin
And I must really be out of touch, because I can't figure out why one INSERT
INSERT into attr_config(id)
SELECT FNBsetid from comp_data

Post by Iann
I've never understood what it is about cursors that makes people want to
use more complex, and slower, constructs such as this temp table loop?
--ian

ASDASDAD

2015-08-13 20:07:01 UTC