0

Is there any way to Declare A cursor inside anonymous Block In SQLSCRIPT .

Animesh Agrawal
  • 161
  • 2
  • 16

2 Answers2

2

Something like this will work:

DO 
BEGIN

DECLARE CURSOR C1 FOR 
        select user_name from users;
declare a nvarchar(256);


   for r as c1 
    DO
    a := r.user_name;
   end for;

  select :a from dummy;
END

That's pretty much what Jobin already proposed. So I am not quite sure why his answer wasn't marked as correct.

Lars Br.
  • 9,949
  • 2
  • 15
  • 29
1

Yes, It is very much possible.

DO 
BEGIN
....
DECLARE CURSOR C1 AS .....
....

END
CodeNotFound
  • 22,153
  • 10
  • 68
  • 69
Jobin
  • 11
  • 1
  • Please try to include some actual code to help the OP – Jeff Feb 12 '16 at 12:14
  • Please add some comments about your solution on why and how it solves the problem – Bhavesh Odedra Feb 12 '16 at 14:37
  • I was trying the same syntax but it was wrong Could not execute 'DO BEGIN DECLARE CURSOR C1 FOR select id from sc_users' SAP DBTech JDBC: [257]: sql syntax error: line 5 col 24 (at pos 62) SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "declare": line 1 col 1 (at pos 1) Could not execute 'for r as c1 DO a := r.id' SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "for": line 1 col 1 (at pos 1) – Animesh Agrawal Feb 18 '16 at 04:55
  • instead of anonymous block i created a stored procedure to solve this problem – Animesh Agrawal Feb 18 '16 at 04:57
  • Please check the documentation before claiming something does not work. Anonymous blocks (DO BEGIN ... END) are only available as of SPS 10 and later. – Lars Br. Feb 18 '16 at 22:00