SQL SP not consistantly generating unique newid()

Question

I am having problems with a stored procedure that i have created. I just started programming with sql about 6 months ago, so my SP might appear to be sloppy, so sorry in advance. Heres the issue i am having: I am selecting the top item in a table, creating a variable that is equal to newid(), and inserting that item into 3 different tables, then i delete the top item, and loop, until all items are inserted/deleted. Occasionally, It seems that when the newid() is generated, it is using the one that was previously generated the last time the stored procedure was run. So i get this error: "23000 (2601):[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert duplicate key row in object 'dbo.lab_nor' with unique index 'ak_lab_nor1'". I output the newid that is generated and checked to see if it if it already existed in the lab_nor table, and it did and it was the previous item i inserted. It's like the newid() isn't updating during the loop. Here's the stored procedure.

@EnterpriseID char (5),
@PracticeID char (4),
@EncounterID uniqueidentifier,
@PersonID uniqueidentifier,
@CurrentProvider varchar (50),
@CurrentProviderID uniqueidentifier,
@LocationID uniqueidentifier,
@CurrentUser varchar (50),
@CurrentUserID int,
@CurrentTime varchar (8)

as

/**********************************
1. Declare Variables
**********************************/

DECLARE @EncounterDate DATETIME,
@MaxRows VARCHAR (100)
SET @EncounterDate = (SELECT enc_timestamp FROM patient_encounter WHERE enc_id = @EncounterID)
SET @MaxRows = (SELECT count(*) FROM rpa_lab_fav_ext2_ WHERE enc_id = @EncounterID)


/**********************************
2. Begin Loop
**********************************/

WHILE @MaxRows > 0

BEGIN

/**********************************
3. Insert top row from rpa_lab_fav_ext2_ into order_
**********************************/

UPDATE TOP(1) rpa_lab_fav_ext2_
    SET txt_order_module_ufo_num = last_generated + 1
        FROM system_counters WITH (UPDLOCK)
            WHERE counter_type = 'lab order'
            AND rpa_lab_fav_ext2_.txt_order_module_ufo_num is null
            AND rpa_lab_fav_ext2_.txt_lab = 'InHouseLab';

        UPDATE system_counters 
            SET last_generated = last_generated + 1
                WHERE counter_type = 'lab order'

        SELECT @MaxRows = count(*) 
            FROM rpa_lab_fav_ext2_     

/**********************************
4. Create unique order id
**********************************/

DECLARE @order_id UNIQUEIDENTIFIER
SET @order_id = NEWID()

--Insert Into RPA_Test_01_ (enterprise_id, practice_id, person_id, created_by, create_timestamp, 
--          modified_by, modify_timestamp, enc_id, txt_test001)

--Select @EnterpriseID, @PracticeID, @PersonID, @CurrentUserID, getdate(), 
--      @CurrentUserID, getdate(), @EncounterID, @order_id

-- select txt_test001 from rpa_test_01_ where enc_id = '9E14B10A-3D76-4EA7-B2E5-C5CB552F3455'
-- select * from lab_nor where order_num = 'CE70A9C4-21F7-42C5-8D44-97BF0EA5AA46'

/**********************************
5. Insert top row from rpa_lab_fav_ext2_ into order_
**********************************/

INSERT INTO order_ (orderedReason, actClass, actCode, actMood, actStatus, actText, actTextDisplay, 
        actDiagnosis, actDiagnosisCode, apptTimeFrame, apptTimeFrameDisp, apptDate, 
        actComments, 
        actRecurTimeInterv, 
        documented_by, encounterDate, ordered, orderedBy, orderedByKey, orderedDate, orderedTime, 
        enterprise_id, practice_id, person_id, encounterID, created_by, create_timestamp, modified_by, 
        modify_timestamp, seq_no, order_module_ufo_num, 
        order_module_order_num, order_module_lab_id)

SELECT TOP(1) txt_lab, 'LAB', txt_lab_code, 'ORD', 'ordered', txt_lab_description, txt_lab_description, 
        txt_assessment, txt_assessment_code, txt_perform_timeframe, txt_perform_timeframe, txt_appt_date, 
        (CASE WHEN txt_cc_provider = null THEN '' ELSE 'CC Results to: ' + txt_cc_provider + ', ' + txt_cc_address + ', ' + txt_cc_city + ', ' + txt_cc_state + ', ' + txt_cc_phone + '.' end), 
        (CASE WHEN txt_interval_ = null THEN '' ELSE 'Interval: ' + txt_interval_ + ', Start: ' + txt_interval_start_date + ', Stop: ' + txt_interval_stop_date end),
        @CurrentUser, @EncounterDate, '1', @CurrentProvider, @CurrentProviderID, getdate(), @CurrentTime,  
        @EnterpriseID, @PracticeID, @PersonID, @EncounterID, @CurrentUserID, getdate(), @CurrentUserID, 
        getdate(), newid(), (CASE WHEN txt_order_module_ufo_num is null THEN null else 'PRO' + txt_order_module_ufo_num end), 
        (CASE WHEN txt_lab = 'InHouseLab' THEN @order_id else null end), (CASE WHEN txt_lab = 'InHouseLab' THEN '2340' else '60' end)

FROM rpa_lab_fav_ext2_
WHERE enc_id = @EncounterID
ORDER BY rpa_lab_fav_ext2_.txt_lab_description asc

/**********************************
6. Insert top row from rpa_lab_fav_ext2_ into lab_nor
**********************************/

IF (select top(1) txt_lab from rpa_lab_fav_ext2_ where enc_id = @EncounterID ORDER BY txt_lab_description) = 'InHouseLab'

BEGIN

INSERT INTO lab_nor (Enterprise_ID, Practice_ID, Enc_ID, Order_num, person_id, Ordering_Provider, 
        test_location, test_status, ngn_status, test_desc, delete_ind, order_control, order_priority, 
        time_entered, spec_action_code, billing_type, ufo_num, lab_id, enc_timestamp, created_by, 
        create_timestamp, modified_by, modify_timestamp, generated_by, order_type, recur_mode, ng_order_ind, 
        documents_ind, signoff_comments_ind, ordered_elsewhere_ind)

SELECT TOP(1) @EnterpriseID, @PracticeID, @EncounterID, @order_id, @PersonID, @CurrentProviderID,
        @LocationID, 'Pending', 'Ordered', txt_lab_description, 'N', 'NW', 'R',
        getdate(), 'N', 'T', 'PRO' + txt_order_module_ufo_num, '2340', @EncounterDate, @CurrentUserID,
        getdate(), @CurrentUserID, getdate(), 'RPA Lab Master', 'L', 'N', 'N',
        'N', 'N', 'N'

FROM rpa_lab_fav_ext2_
WHERE enc_id = @EncounterID
ORDER BY txt_lab_description asc

END


/**********************************
7. Insert top row from rpa_lab_fav_ext2_ into lab_order_tests
**********************************/

IF (select top(1) txt_lab from rpa_lab_fav_ext2_ where enc_id = @EncounterID ORDER BY txt_lab_description) = 'InHouseLab'

BEGIN

INSERT INTO lab_order_tests (order_test_id, order_num, test_code_id, test_code_text, test_code_system, 
        created_by, create_timestamp, modified_by, modify_timestamp)

SELECT top(1) newid(), @order_id, txt_lab_code, txt_lab_description, 'I',
        @CurrentUserID, GETDATE(), @CurrentUserID, GETDATE()

FROM rpa_lab_fav_ext2_
WHERE enc_id = @EncounterID
ORDER BY txt_lab_description asc

END


/**********************************
8. Delete top row from rpa_lab_fav_ext2_
**********************************/

--DELETE TOP (1) 
--FROM rpa_lab_fav_ext2_ 
--WHERE txt_lab_description IN
--  (SELECT TOP 1 txt_lab_description
--      FROM rpa_lab_fav_ext2_ 
--      WHERE enc_id = @EncounterID
--      ORDER BY txt_lab_description asc)

Declare @Delete varchar (20)
Set @Delete = (select top(1) txt_lab_code from rpa_lab_fav_ext2_ where enc_id = @EncounterID order by txt_lab_description asc)

DELETE rpa_lab_fav_ext2_
WHERE enc_id = @EncounterID
and txt_lab_code = @Delete

/**********************************
9. Find row count
**********************************/

SET @MaxRows = (SELECT count(*) FROM rpa_lab_fav_ext2_ WHERE enc_id = @EncounterID)

/**********************************
10. If max rows is 0, end loop, else continue
**********************************/

IF @MaxRows = 0
    BREAK
ELSE
    CONTINUE

END

~~~~~Edit It seems as if the @Order_ID variable doesn't always reset. If i run the SP and only insert once before ending the loop, it goes fine. If i run it and try to insert twice before ending, i get the error. I tried using both SET and SELECT on my variables, but both have the same problem.

Answer 1

This is odd as this article from Microsoft says that the NEWID() should always generate a unique id since it uses the MAC address and CPU time. It could be a bug if it can be reproduced.