@@ROWCOUNT does not work as expected

Question

I have an application that is not working as intended. We have found a system error, but the vendor claims that this issue cannot be fixed.

The issue is related to data getting overwritten in the database.

The system collects data from an external datasource on a daily basis. Data that are collected contains data records for the last 3 days.

A SQL Insert do overwrite existing data already inserted in the SQL database, but can this really be true that a stored procedure cannot prevent data been overwriten?

the table dbo.PointValue contains the following:

PointID DataTime    DataValue   DataValueType   DataValueStatus
32  2015-08-14 23:00:00.000 8,07    NULL    NULL

If point ID and DataTime is present, then data should not be inserted.

I believe that this part of the stored procedure that is likely to cause this issue

UPDATE PointValue 
SET 
DataValue = @data_val,
DataValueType = @data_value_type,
DataValueStatus = @data_value_status
WHERE (PointID = @point_id) AND (DataTime = @data_time)

IF @@ROWCOUNT = 0

but I'm no expert in SQL and stored procedures.

Please, any input on how to prevent data getting overwritten is more than welcome.

Complet store procedure below:

USE [i96X]
GO
/****** Object:  StoredProcedure [dbo].[usp_insertLogDataXML]    Script Date: 23-11-2015 10:33:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[usp_insertLogDataXML]
@xml VARCHAR(MAX)
AS

DECLARE @iDoc INT -- A handle to the prepared XML document

-- Prepare the XML document in memory
SET NOCOUNT ON
EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @xml

-- 18/02/2005 : We may find a condition where the XML document contains 2 duplicate times for
-- the same point id, this is likely to happen at the summer time -> standard time change. We
-- cannot violate the primary key constraint, so make sure we check whether a value already
-- exists for this point id and time before inserting.

--
-- 07/07/2006
-- DataValue type changed from float to varchar(10)
-- and converted back to float after replacing comma with decimal point
--

DECLARE log_data_cursor CURSOR FOR
SELECT theXML.PointID, theXML.DataTime, theXML.DataValue, theXML.DataValueType, theXML.DataValueStatus
FROM OpenXML(@iDoc, '/root/P',1) WITH
(
PointID         int     '@i',
DataTime        DATETIME    '@t',
DataValue       varchar(10) '@v',
DataValueType   int '@y',
DataValueStatus int '@s'
) theXML


DECLARE @point_id int
DECLARE @data_time DATETIME
DECLARE @data_val varchar(10)
DECLARE @data_value float

DECLARE @data_value_type int
DECLARE @data_value_status int


OPEN log_data_cursor

FETCH NEXT FROM log_data_cursor
INTO @point_id, @data_time, @data_val, @data_value_type, @data_value_status

WHILE @@FETCH_STATUS = 0
BEGIN

-- replace the , with . in @data_val
SET @data_val = REPLACE(@data_val,N',',N'.')

-- change the @data_val here to float
SET @data_value = CAST(@data_val AS float)

-- if data type and status is equal to -1, then set them to NULL
IF @data_value_type = -1
    SET @data_value_type = NULL

IF @data_value_status = -1
    SET @data_value_status = NULL

UPDATE PointValue 
SET 
DataValue = @data_val,
DataValueType = @data_value_type,
DataValueStatus = @data_value_status
WHERE (PointID = @point_id) AND (DataTime = @data_time)

IF @@ROWCOUNT = 0
BEGIN

    -- Nothing already there for this point / time so we are
    -- safe to do an insert.

    INSERT INTO PointValue (PointID, DataTime, DataValue, DataValueType, DataValueStatus)
    VALUES (@point_id, @data_time, @data_val, @data_value_type, @data_value_status)
END

FETCH NEXT FROM log_data_cursor
INTO @point_id, @data_time, @data_val, @data_value_type, @data_value_status
END

CLOSE log_data_cursor
DEALLOCATE log_data_cursor

-- Remove the XML document
EXECUTE sp_xml_removedocument @iDoc

Answer 1

The UPDATE block may fails due to the AND (DataTime = @data_time) in the WHERE clause. Because the DataTime contains the timestamp and @data_time may not contain the timestamp and its possible to fails.

The example will help you to understand:

-- Create the temporary table 
CREATE TABLE #DateTimeTest(DataTime DATETIME)
-- Inserting few entries for testing
INSERT INTO #DateTimeTest (DataTime) 
VALUES ('2015-11-23 04:55:00'), ('2015-11-23 05:00:00'), ('2016-11-24 06:00:00')
-- Declare the datetime variable
DECLARE @TestDataTime AS DATETIME = '2015-11-23';
-- Select the records for the given datetime variable
SELECT * FROM #DateTimeTest WHERE DataTime = @TestDataTime
-- Drop the temp table
DROP TABLE #DateTimeTest

Here the SELECT * FROM #DateTimeTest WHERE DataTime = @TestDataTime doesn't return data, even if I have the records for 2015-11-23.

But if you add the time period the below query will return the data:

SELECT * FROM #DateTimeTest 
WHERE DataTime BETWEEN @TestDataTime + ' 00:00:00' AND @TestDataTime + ' 23:59:59'

So in your code if you change the UPDATE block as below it will work:

UPDATE PointValue 
SET 
DataValue = @data_val,
DataValueType = @data_value_type,
DataValueStatus = @data_value_status
WHERE (PointID = @point_id) 
     AND (DataTime BETWEEN @data_time + ' 00:00:00' AND @data_time + ' 23:59:59')