Click here to Skip to main content
15,891,657 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
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

SQL
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

SQL
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)
Posted
Updated 4-Sep-13 6:05am
v2
Comments
Mike Meinz 4-Sep-13 13:31pm    
Is there an exception raised? What is it?

Have you stepped through each line using the interactive debugger? What happens when it gets to the Insert in question?

I would take out all of the PRINT statements and then try it.
anurag19289 4-Sep-13 13:42pm    
Hello Mike- When i execute this no exception is raised..



I have never used interactive debugger.. i m using print statement to debug..how to use this interactive debugger


i get in message as :


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


--finally after the completion of my cursor there is a select statement..
--but i m not getting any result
anurag19289 4-Sep-13 13:44pm    
Mike -

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)

This statement is correct right. A identity i m storing in @NEW_ROUTEID and that i am using immediately after in the insert statement. Is this correct ?
Mike Meinz 4-Sep-13 13:48pm    
I would take out all of the PRINT statements and then try it.
Mike Meinz 4-Sep-13 13:50pm    
SQL Server Management Studio has a Debug menu item. You could paste all of the SQL in a query window and use the SQL Server Management Studio interactive debugger to step through the statements.

I have never seen the PRINT statement used and suspect that it is causing an issue.

1 solution

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
 
Share this answer
 
Comments
anurag19289 4-Sep-13 14:58pm    
ok mubin let me try that in office tomorrow...and i will get back to you
anurag19289 5-Sep-13 8:08am    
Thanks :) it worked

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900