• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 898
  • Last Modified:

Adding cursor and "if exists"

This is related to the stored proc i posted here:

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

  • 3
1 Solution
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;
CamilliaAuthor Commented:
let me try
CamilliaAuthor 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;"
CamilliaAuthor Commented:
thanks, i got it working

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now