Problem with a select in a stored procedure

Does anybody know what is wrong with this code from a stored procedure:
DECLARE tables_cursor CURSOR FOR
SELECT tf_change_out_id, destination, re_table, date_in
FROM tf_change_out_table
WHERE date_out = NULL
ORDER BY tf_change_out_id

Here is the error I'm getting:
Error 107: The column prefix 'tf_change_out_table' does not match with a table name or alias name used in the query.Perhaps there's something before the cursor declaration that results in the error below. If you'd select the cursor-declaration, and parse it, is there an error message?|||Silly me! The error was further down the code. Why can't they give line numbers with all the errors?|||That woud make it too easy, and then everyone would think they could write SQL :D


Besides ... if it was hard to write, it should be hard to read ;)

problem while declaring a a cursor

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

Quote:

Originally Posted by 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

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|||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.