是否可以在生成标识号后立即插入。 [英] Is it possible to insert immediately after the identity number generated.

问题描述

  BEGIN 尝试
  BEGIN   TRANSACTION  
 
  DECLARE  CUR_MOVE  CURSOR   FOR  
  SELECT  USERID，Appointment_DateTime，REQUEST_ID，From_Facility_ID，FROM_ADDRESS_ID，TO_ADDRESS_ID，TO_FACILITY_ID  FROM ＃SINGLEREQUEST3 
  OPEN  CUR_MOVE 
  FETCH  CUR_MOVE  INTO   @ USERID ， @ APPDATE ， @ REQUESTID ， @ From_Facility_ID ， @ FROM_ADDRESS ， @ TO_ADDRESS ， @ TO_FACILITY_ID  
 
  WHILE   @@ Fetch_Status  =  0  
  BEGIN  
 
  PRINT  '  @ USERID  - >' + CAST（ @ USERID   AS   VARCHAR ）
  PRINT  '  @ APPDATE  - >' + CAST（ @ APPDATE   AS   VARCHAR ）
 
 < span class =code-keyword> PRINT  '  @ FROM_ADDRESS  - >' + CAST（ @ FROM_ADDRESS   AS   VARCHAR ）
  PRINT   '  @ TO_ADDRESS  - >' + CAST（ @ TO_ADDRESS   AS   VARCHAR ）
  PRINT  '  @ TO_FACILITY_ID  - >' + CAST（ @ TO_FACILITY_ID   AS   VARCHAR ）
 
 
  IF （ @ TO_FACILITY_ID   IS   NOT   NULL ）
  BEGIN  
  INSERT   INTO  TBL_ROUTE_HEADER（Route_Type_ID，Hub_ID，Route_Status_ID，Current_Occupancy，Appointment_DateTime，CREATEd_BY，Created_On）
  VALUES （  1 ， @ HUBID ， 1 ，< span class =code-digit> 1 ， @ APPDATE ，'  ADMIN'，GETDATE（））
  set   @ NEW_ROUTEID  =  @@ identity  
  PRINT   '  @ NEW_ROUTEID：' + CAST（ @ NEW_ROUTEID   AS   VARCHAR （ 50 ））
 
  INSERT   INTO  TBL_ROUTE_STOP_DETAIL（Route_ID，Stop_Number，Address_ID） VALUES （ @ NEW_ROUTEID ， 1 ， @ FROM_ADDRESS ）
 
  INSERT   INTO  TBL_ROUTE_STOP_DETAIL（Route_ID，Stop_Number，Address_ID ）
  VALUES （ @ NEW_ROUTEID ， 2 ， @ TO_ADDRESS ）
  set   @NEW_STOPID  =  @@ identity  
  PRINT  '  @ NEW_STOPID：' + CAST（ @ NEW_STOPID   AS   VARCHAR （ 50 ））
  INSERT   INTO  TBL_ROUTE_STOP_DETAIL（Route_ID，Stop_Number，Address_ID）
  VALUES （ @NEW_ROUTEID ， 3 ， @ FROM_ADDRESS ）
  set   @ NEW_STOPIDF  =  @@ identity  
 
 
  PRINT  '  @ NEW_STOPIDF：' + CAST（ @ NEW_STOPIDF   AS   VARCHAR （ 50 ））

- 当我执行它时停在附近

 TYPE：P 
 fromdate：Sep  7   2013  12:00 AM 
 todate：Sep  7   2013  12:00 AM 
  @ USERID    -   > 51333357  
  @ APPDATE    -   > 2013年9月7日上午6:30  
  @ FROM_ADDRESS    -   > 206950  
  @ TO_ADDRESS    -   > 65597  
 < span class =code-sdkkeyword> @ TO_FACILITY_ID    -   > 639  
 
 @NEW_ROUTEID：880315 

- 在此打印声明后不插入



< pre lang =sql> set @ NEW_ROUTEID = @ @identity
PRINT &＃39; @NEW_ROUTEID：&＃39; + CAST（ @ NEW_ROUTEID AS VARCHAR （ 50 ））

INSERT INTO TBL_ROUTE_STOP_DETAIL（Route_ID，Stop_Number，Address_ID） VALUES （ @ NEW_ROUTEID ， 1 ， @ FROM_ADDRESS ）

解决方案

您好您可以使用以下语法在变量中设置标识值



选择@ NEW_ROUTEID = SCOPE_IDENTITY（）



取代使用这个

设置@NEW_ROUTEID = @@身份



问候

Mubin

BEGIN TRY
BEGIN TRANSACTION

    DECLARE CUR_MOVE CURSOR FOR
    SELECT USERID,Appointment_DateTime,REQUEST_ID,From_Facility_ID,FROM_ADDRESS_ID,TO_ADDRESS_ID,TO_FACILITY_ID FROM #SINGLEREQUEST3
    OPEN CUR_MOVE
    FETCH CUR_MOVE INTO @USERID,@APPDATE,@REQUESTID,@From_Facility_ID,@FROM_ADDRESS,@TO_ADDRESS,@TO_FACILITY_ID

            WHILE @@Fetch_Status = 0
            BEGIN

            PRINT '@USERID-->' + CAST(@USERID AS VARCHAR)
            PRINT '@APPDATE-->' + CAST(@APPDATE AS VARCHAR)

            PRINT '@FROM_ADDRESS-->' + CAST(@FROM_ADDRESS AS VARCHAR)
            PRINT '@TO_ADDRESS-->' + CAST(@TO_ADDRESS AS VARCHAR)
            PRINT '@TO_FACILITY_ID-->' + CAST(@TO_FACILITY_ID AS VARCHAR)


                    IF(@TO_FACILITY_ID IS  NOT NULL)
                    BEGIN
                        INSERT INTO TBL_ROUTE_HEADER (Route_Type_ID,Hub_ID,Route_Status_ID,Current_Occupancy,Appointment_DateTime,CREATEd_BY,Created_On)
                        VALUES(1,@HUBID,1,1,@APPDATE,'ADMIN',GETDATE())
                        set @NEW_ROUTEID = @@identity
                        PRINT '@NEW_ROUTEID:' + CAST(@NEW_ROUTEID AS VARCHAR(50))

                        INSERT INTO TBL_ROUTE_STOP_DETAIL (Route_ID,Stop_Number,Address_ID)VALUES(@NEW_ROUTEID,1,@FROM_ADDRESS)

                        INSERT INTO TBL_ROUTE_STOP_DETAIL (Route_ID,Stop_Number,Address_ID)
                        VALUES(@NEW_ROUTEID,2,@TO_ADDRESS)
                        set @NEW_STOPID = @@identity
                        PRINT '@NEW_STOPID:' + CAST(@NEW_STOPID AS VARCHAR(50))
                        INSERT INTO TBL_ROUTE_STOP_DETAIL (Route_ID,Stop_Number,Address_ID)
                        VALUES(@NEW_ROUTEID,3,@FROM_ADDRESS)
                        set @NEW_STOPIDF = @@identity


                        PRINT '@NEW_STOPIDF:' + CAST(@NEW_STOPIDF AS VARCHAR(50))

--when i execute it stops near

TYPE:P
fromdate:Sep  7 2013 12:00AM
todate:Sep  7 2013 12:00AM
@USERID-->51333357
@APPDATE-->Sep  7 2013  6:30AM
@FROM_ADDRESS-->206950
@TO_ADDRESS-->65597
@TO_FACILITY_ID-->639
 
@NEW_ROUTEID:880315

--it doesnot insert after this print statement

set @NEW_ROUTEID = @@identity
                        PRINT '@NEW_ROUTEID:' + CAST(@NEW_ROUTEID AS VARCHAR(50))

                        INSERT INTO TBL_ROUTE_STOP_DETAIL (Route_ID,Stop_Number,Address_ID)VALUES(@NEW_ROUTEID,1,@FROM_ADDRESS)

解决方案

Hi you can use following syntax to set identity value in your variable

select @NEW_ROUTEID=SCOPE_IDENTITY ()

inplace of using this
set @NEW_ROUTEID = @@identity

Regards
Mubin

这篇关于是否可以在生成标识号后立即插入。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！