SQL SP 不一致地生成唯一的 newid()

Question

我创建的存储过程遇到问题。我大约 6 个月前才开始使用 sql 编程，所以我的 SP 可能显得很马虎，所以提前抱歉。这是我遇到的问题：我选择表中的顶部项目，创建一个等于 newid() 的变量，并将该项目插入到 3 个不同的表中，然后删除顶部项目，然后循环，直到所有项目被插入/删除。有时，当生成 newid() 时，它似乎使用的是上次运行存储过程时生成的 ID。所以我收到此错误：“23000 (2601):[Microsoft][ODBC SQL Server Driver][SQL Server]无法在具有唯一索引 'ak_lab_nor1' 的对象 'dbo.lab_nor' 中插入重复的键行”。我输出生成的 newid 并检查它是否已存在于 lab_nor 表中，并且它确实存在，并且它是我插入的上一个项目。就像 newid() 在循环期间没有更新一样。这是存储过程。

@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

~~~~~~编辑 似乎 @Order_ID 变量并不总是重置。如果我运行 SP 并且在结束循环之前只插入一次，那么一切都会很好。如果我运行它并尝试在结束之前插入两次，我会收到错误。我尝试对变量同时使用 SET 和 SELECT，但两者都有相同的问题。

Answer 1

这很奇怪，因为 Microsoft 的 这篇 文章说 NEWID() 应该总是生成一个唯一的 ID，因为它使用 MAC 地址和 CPU 时间。如果可以重现，则可能是一个错误。