problem while declaring a a cursor

[AnuBGD]

The sql block is
=================
set pagesize 5000;
set serveroutput on
set linesize 200;
set heading off;
set feedback off;

DECLARE cursor1 CURSOR
FOR
SELECT element_id,element_type,name from NH_ELEMENT where name like '%DTH%';
end;
/
=====================
Error I get is :-
=====================
FOR
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "FOR" when expecting one of the following:
:= . ( @ % ; not null range default character
=====================

Can anyone figure out what's going wrong here?

Cheers,
Anu

[mivey4]

The sql block is
=================
set pagesize 5000;
set serveroutput on
set linesize 200;
set heading off;
set feedback off;

DECLARE cursor1 CURSOR
FOR
SELECT element_id,element_type,name from NH_ELEMENT where name like '%DTH%';
end;
/
=====================
Error I get is :-
=====================
FOR
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "FOR" when expecting one of the following:
:= . ( @ % ; not null range default character
=====================

Can anyone figure out what's going wrong here?

Cheers,
Anu

Just curious why you are placing the / character at the end of your cursor body? My guess is that you are attempting to create the cursor based off the ORACLE format which won't work in MSSQL.

The proper model would be

DECLARE cursor1 CURSOR
FOR

[mivey4]

First you need to declare variables to store the returned content of the cursor, which is very different from a stored procedure. I will make some assumptions here to recreate your proposed cursor since I don't know your actual datatypes.

The proper Cursor model would be

Declare
@element_id int
,@element_type int
,@name varchar(20)

DECLARE cursor1 CURSOR
FOR
SELECT element_id,element_type,name from NH_ELEMENT where name
like '%DTH%';
OPEN cursor1
FETCH NEXT FROM cursor1 INTO
@element_id, @element_type, @name
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- Do something with the variables here like:
print cast(@element_id as varchar(3))
END
CLOSE cursor1
DEALLOCATE cursor1
END

But I believe what you're actually trying to do is create a procedure, not a cursor. Cursors are typically discouraged unless absolutely necessary due to the overhead and a simple select statement certainly wouldn't merit the use of a cursor. Try using a procedure instead.


What database platform are you using? This forum is for SQL not ORACLE.