We help IT Professionals succeed at work.

Adding cursor and "if exists"

Camillia
Camillia asked
on
This is related to the stored proc i posted here:
http://www.experts-exchange.com/Database/DB2/Q_23105191.html

Right before the Declare c1 Cursor, I need to do a check for userlogin and see if it exists in the database. If not, I need to set it to a value. I know in a stored proc , Declares have to be grouped together on top.
So, anyway i can add this check?

I tired "if exists" with the cursor and it didnt work, so I tried code below. That doesnt give me an error. Then I open s1. But how do i check to see if T1User is in s1 or not?
DECLARE s1 CURSOR WITH RETURN FOR  select T1user from taborder where t1user = UserLogin fetch first 1 rows only;
 
Declare c1 cursor with return  for select ....
....
 
open s1;
 
open c1;

Open in new window

Comment
Watch Question

in order to check if the user exists, you don't need to use a cursor,
when you retrieve only a single record you can use a single ton select.
it's easier and better performance wise
try this

declare userInd int

Declare c1 cursor with return  for select ....
....
select count(*) into userInd from taborder where t1user = UserLogin fetch first 1 rows only;

if userInd = 1 then ...,

else ...
 
open c1;
BRONZE EXPERT

Author

Commented:
let me try
BRONZE EXPERT

Author

Commented:
I have this now:
Declare cursor...
 select count(*) into UserInd from taborder where t1user = UserLogin fetch first 1 rows only;
  If userInd = 0 Then
  set UserLogin ='*System'
 END IF ;  
 OPEN c1;

** why do I get : Keyword END not expected. If I comment out that if-section, it runs fine. I also tried "end;" and not "end if;"
BRONZE EXPERT

Author

Commented:
thanks, i got it working

Explore More ContentExplore courses, solutions, and other research materials related to this topic.