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
@APPDATE
@FROM_ADDRESS
@TO_ADDRESS
@TO_FACILITY_ID
@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)