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

Need help with not exists

Can someone take a look at my not exists statement. I think it is hanging when I run my sql stored procedure.
Look at my not exists statement : IF NOT EXISTS(SELECT FUAD_ITS_CLERK_ID = @lchFITS_PREFIX + RandomId())
It does not look right to me, so maybe I just don't understand how to do it properly.


OPEN CURS_EMP
@lchFITS_SUFFIX= CONVERT(INT, @lchSuffix)
FETCH NEXT FROM CURS_EMP
INTO @lchPSUS_USER_NUM, @lchFITS_STATE_CD, @lchFITS_PREFIX
SELECT lchFITS_SUFFIX = @lchFITS_SUFFIX+1

WHILE @@FETCH_STATUS = 0
BEGIN
        SET @ClerkIdIsUnique = 0
        WHILE(@ClerkIdIsUnique = 0)
        BEGIN
             IF NOT EXISTS(SELECT FUAD_ITS_CLERK_ID = @lchFITS_PREFIX + RandomId())
            BEGIN
                     UPDATE SMT_FCTS_FUAD_TOPL_USR SET
                        FUAD_ITS_CLERK_ID =  @lchFITS_PREFIX + RandomId()
                         WHERE PSUS_USER_NUM = @lchPSUS_USER_NUM
                         AND (FUAD_ITS_CLERK_ID = @lchPSUS_USER_NUM
                     
                         UPDATE SMT_FCTS_FITS_STATE_CDS
                         SET FITS_SUFFIX = CONVERT(VARCHAR, @lchFITS_SUFFIX)
                         FETCH NEXT FROM CURS_EMP
                    INTO @lchPSUS_USER_NUM, @lchFITS_STATE_CD, @lchFITS_PREFIX
                         SET @ClerkIdIsUnique = 1
                         SELECT @lchFITS_SUFFIX = @lchFITS_SUFFIX+1
               END
               ELSE SELECT @lchFITS_SUFFIX = @lchFITS_SUFFIX+1
          END
0
brgdotnet
Asked:
brgdotnet
2 Solutions
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I'm assuming RandomID() is a User Defined Function.

Maybe try assigning RandomID() to a variable and use that in the Select statement - see if that is better.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Definitively, you need to post here the code of RandomID function or else we won't be able to help you.
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

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