@@rowcount not giving correct value after a insert

Question

I using this query to insert a row in the table tblWorkOrders. But @@ROWCOUNT is giving 0 eventhough a row is inserted into the table. What may be the reason? how to change the query to give proper @@ROWCOUNT?

INSERT INTO tblWorkOrders
        (           WO_Type , WO_Yard , WO_Operation , WO_Source , WO_SourceID , WO_SourceXLocation , WO_SourceYLocation , 
                    WO_Destination , WO_DestinationID , WO_DestinationXLoc, WO_DestinationYLoc , WO_Crane , WO_Priority , 
                    WO_ScheduledAt , WO_CoilNo , WO_ExecutionStart , WO_ExecutionEnd , WO_ExecutedBy , WO_Remarks,WO_RequestRefID )

        ( SELECT    OM_WOType,OM_Yard,OM_OperationsID,'Saddle' ,STARTINGPOINT.NAME,STARTINGPOINT.XAxis,STARTINGPOINT.YAxis,
                    'Saddle',ENDINGPOINT.NAME,ENDINGPOINT.XAxis,ENDINGPOINT.YAxis,OM_AssignedCrane,NULL,
                    NULL,@COILNO,NULL,NULL,NULL,NULL,NULL
                    FROM 

                    tblOperationsMaster OPM

                    INNER JOIN 
                    (SELECT SP_OperatingPointID AS [NAME],SP_XLocation AS [XAxis],SP_YLocation AS [YAxis] FROM tblStandardPoints
                    UNION ALL
                    SELECT SM_SaddleID AS [NAME],SM_XCoordinate AS [XAxis],SM_YCoordinate AS [YAxis] FROM tblSaddleMaster) STARTINGPOINT
                    ON STARTINGPOINT.NAME = @SOURCEIDFROMCALLINGFN

                    INNER JOIN 
                    (SELECT SP_OperatingPointID AS [NAME],SP_XLocation AS [XAxis],SP_YLocation AS [YAxis] FROM tblStandardPoints
                    UNION ALL
                    SELECT SM_SaddleID AS [NAME],SM_XCoordinate AS [XAxis],SM_YCoordinate AS [YAxis] FROM tblSaddleMaster) ENDINGPOINT 
                    ON ENDINGPOINT.NAME = @DESTINATIONIDFROMCALLINGFN

                    WHERE OPM.OM_OperationsID = @OPERATIONID)

        SET @ROWSINSERTED = @@ROWCOUNT;

Answer 1

Hi you will get rowcount by this:

SELECT @ROWSINSERTED =@@ROWCOUNT

Hope it helps