CODEWITHSUNDEEP
sqlsql-server-2012rowcount
Olivarsham
Olivarsham

Reputation: 1731

@@rowcount not giving correct value after a insert

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;

Upvotes: 0

Views: 232

Answers (3)

Neeraj
Neeraj

Reputation: 4489

Hi you will get rowcount by this:

SELECT @ROWSINSERTED =@@ROWCOUNT

Hope it helps

Upvotes: 2

Arunprasanth K V
Arunprasanth K V

Reputation: 21931

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)
DECLARE @Rows int
SELECT @Rows=@@ROWCOUNT
SELECT @Rows AS Rows,@@ROWCOUNT AS [ROWCOUNT]

Try this i think it will work

Upvotes: 1

Manish Sharma
Manish Sharma

Reputation: 2426

try this,

i have provided example check it.

use this after insert.

SELECT @ROWSINSERTED=@@ROWCOUNT
SELECT @ROWSINSERTED AS Rows

Upvotes: 1

Related Questions