troubleshooting Question

Can an expert review 5 lines of sql for me please.

Avatar of brgdotnet
brgdotnetFlag for United States of America asked on
Microsoft SQL Server
1 Comment1 Solution116 ViewsLast Modified:
Can someone take a look at my Sql below at lines 32 through 36. Basically I want the code to loop on lines 32 thru 36 until @Flag = 1
Meaning if the value for @lchFITS_SUFFIX is not found in column SMT_FCTS_FITS_STATE_CDS then set @Flag = 1 and quit incrementing
@lchFITS_SUFFIX by 1.

The query analyzer does not throw an error so the code appears to work, however I have not executed it against actual data. So if
someone can look at my new code, lines 32 through 36, I would appreciate it.

Lines 32 through 36 is new code I wrote. This code is inside a stored procedure that uses a cursor. The longer version of the stored procedure is
below the header "Stored Proc" listed below. You can just ignore that code, I just included it for reference.

-- Please experts, check on my code on lines 32 through 36 below to see if it is okay what I am doing.

28     DECLARE @Flag INT
29     SET @Flag = 0;

30     WHILE @@FETCH_STATUS = 0
31     BEGIN
32        WHILE @Flag = 0
33        BEGIN
34              SELECT @lchFITS_SUFFIX = @lchFITS_SUFFIX+1            
35              IF NOT EXISTS(SELECT FITS_SUFFIX FROM SMT_FCTS_FITS_STATE_CDS WHERE FITS_SUFFIX = @lchFITS_SUFFIX) @Flag = 1
36        END

37           UPDATE SMT_FCTS_FUAD_FOOTETS_USR SET
38           FUAD_ITS_CLERK_ID = @lchFITS_SUFFIX
39           WHERE PSUS_USER_NUM = @lchPSUS_USER_NUM
40           AND (FUAD_ITS_CLERK_ID = ' ' OR FUAD_ITS_CLERK_ID IS NULL


41        FETCH NEXT FROM CURS_EMP
42        INTO @lchPSUS_USER_NUM, @lchFITS_STATE_CD, @lchFITS_PREFIX

43     END

--=================================================================
--   S T O R E D   P R O C  (Just ignore the code below, I just included it to show that the stored procedure is using a cursor.
--=================================================================
1 DECLARE CURS_STATE CURSOR
2 FOR
3    SELECT FITS_STATE_CD, FITS_PREFIX, FITS_SUFFIX
4    FROM SMT_FCTS_FITS_STATE_CDS FITS
5   WHERE FITS.FITS_ACTIVE = 1
6   ORDER BY FITS_STATE_CD

7 OPEN CURS_STATE
8 FETCH NEXT FROM CURS_STATE
9 INTO @lchStateCode, @lchPrefix, @lchSuffix

10  WHILE @@FETCH_STATUS = 0
11  BEGIN
12     DECLARE CURS_EMP CURSOR
13     FOR
14     SELECT
15     USTY.PSUS_USER_NUM AS PSUS_USER_NUM,
16     USTY.FITS_STATE_CD AS FITS_STATE_CD,
17     FITS.FITS_PREFIX AS FITS_PREFIX
18     FROM SMT_FCTS_FUAD_FOOTETS_USR USTY,
19     SMT_FCTS_FITS_STATE_CDS FITS
20     WHERE USTY.FITS_STATE_CD = FITS.FITS_STATE_CD
21     AND FITS.FITS_ACTIVE = 1
22     AND USTY.FITS_STATE_CD = @lchStateCode
23     AND (USTY.FUAD_ITS_CLERK_ID = '' OR USTY.FUAD_ITS_CLERK_ID IS NULL)
24     AND USTY.PSTE_CODE = @lchProdSteCode
25     ORDER BY USTY.PSUS_USER_NUM

26     OPEN CURS_EMP
27     SELECT @lchFITS_SUFFIX = CONVERT(INT, @lchSuffix)
28     FETCH NEXT FROM CURS_EMP
29     INTO @lchPSUS_USER_NUM, @lchFITS_STATE_CD, @lchFITS_PREFIX

30     WHILE @@FETCH_STATUS = 0
31     BEGIN
32        SELECT @lchFITS_SUFFIX = @lchFITS_SUFFIX+1

33      UPDATE SMT_FCTS_FUAD_FOOTETS_USR SET
34        FUAD_ITS_CLERK_ID = @lchFITS_SUFFIX
35       WHERE PSUS_USER_NUM = @lchPSUS_USER_NUM
36       AND (FUAD_ITS_CLERK_ID = ' ' OR FUAD_ITS_CLERK_ID IS NULL


37        FETCH NEXT FROM CURS_EMP
38        INTO @lchPSUS_USER_NUM, @lchFITS_STATE_CD, @lchFITS_PREFIX

39       END

40   CLOSE CURS_EMP
41   DEALLOCATE CURS_EMP
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros