1

I use a custom ORM generator that calls stored procedures, and validates the number of rows affected by UPDATE and DELETE statements using ExecuteNonQuery(), along these lines:

// Execute the stored procedure and get the number of rows affected.
int result = command.ExecuteNonQuery();

// Exactly one row was deleted, as expected.
if (result == 1)
    return true;

// No rows were deleted.  Maybe another user beat us to it.  Fine.
if (result == 0)
    return false;

// We don't know how many rows were deleted.  Oh well.
if (result == -1)
    return false;

// Something awful has happened; it's probably a bug in the stored procedure.
throw new Exception("Too many rows were deleted!");

When my stored procedures are mundane T-SQL updates and deletes against local tables, this system works fine. 

CREATE PROCEDURE [widgets].[Update] 
    @WidgetID int,
    @NewName varchar(10)
AS
BEGIN

    UPDATE Widgets SET Name = @NewName WHERE WidgetID = @WidgetID

END

However, sometimes I need to EXEC against a Linked Server:

CREATE PROCEDURE [widgets].[Update] 
    @WidgetID int,
    @NewName varchar(10)
AS
BEGIN

    DECLARE @OpenQuery varchar(max)
    SET @OpenQuery = 'execute function mydata:widgets_Update(' +  CAST(@WidgetID as varchar())+  ','''  + @NewName + ''')'

    DECLARE @Query varchar(max)
    SET @Query = 'SELECT * FROM OPENQUERY(INFORMIX, ''' + @OpenQuery +''')'

    EXEC (@Query)

END

If I'm not directly issuing INSERT, UPDATE or DELETE statements in T-SQL, SQL Server (by design) returns the value -1 to ExecuteNonQuery() via the DONE_IN_PROC token. My ORM code can't do anything useful with this, so I'm willing to cheat a little.

First, I modify the remote query on the linked server to return the number of affected rows as an integer. For the stored procedure widgets_Update() on my remote Informix server, for example, I'll add this to the end:

-- Return the number of rows affected.
return DBINFO('sqlca.sqlerrd2');

Then I consume that number in order fake out the @@ROWCOUNT/DONE_IN_PROC mechanism:

-- Turn off row counts for the moment.
SET NOCOUNT ON

-- Create a dummy table to get the result from EXEC into a local variable
DECLARE @Rowcount Table(n int)
INSERT @Rowcount EXEC (@Query)
DECLARE @N int = (SELECT n FROM @Rowcount)

-- Create a dummy table to receive the inserted rows.
DECLARE @Table table (n int)

-- Modify the number of Affected Rows returned in the DONE_IN_PROC token by inserting exactly the right number of dummy rows into a dummy table. 
SET NOCOUNT OFF 
INSERT @Table SELECT * FROM ModifyROWCOUNT(@N)

The inline table-valued function ModifyROWCOUNT() simply generates empty rows (in the spirit of a numbers table) on the fly, using code I cribbed from another answer:

CREATE FUNCTION [dbo].[ModifyROWCOUNT]
(   
    @Rowcount int
)
RETURNS TABLE 
AS
RETURN 
(
    WITH
      L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), -- 2^1
      L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),       -- 2^2
      L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),       -- 2^4
      L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),       -- 2^8
      L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),       -- 2^16
      L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),       -- 2^32
      Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS k FROM L5)

    SELECT null as n
    FROM nums
    WHERE k <= @Rowcount
)

So far, this approach seems to work, and performs just fine; however, I've only tested it when updating or deleting one record at a time. Fortunately, this is the primary use case. My concern is that the code-to-effect ratio is so high that this solution smells bad to me, and I have a significant number of remote queries to build out this way.

My question is this: If my stored procedure must use EXEC and OPENQUERY to insert, update or delete records on a remote server, is there a superior way to return the actual affected row count from T-SQL so I can consume it with ExecuteNonQuery()?

Please assume that I can't add OUTPUT parameters to these stored procedures, which would require the use of ExecuteScalar() instead. And note that there are mechanisms (namely SELECT statements) that will modify @@ROWCOUNT, but still return -1 in the DONE_IN_PROC token.

Mat
  • 470
  • 7
  • 12
  • These two statements appear contradictory. "I use a custom ORM generator" ... "I can't add OUTPUT parameters" If you have a custom ORM, give it an option to retrieve the rowcount from a parameter or a resultset. A simple solution would be to always use ExecuteReader, and if the procedure returns a resultset of a certain shape, retrieve the rowcount from there instead. – David Browne - Microsoft Dec 08 '17 at 17:45
  • You're right, I *could*, but I really don't want to. The T-SQL stored procedures function as an abstraction layer, because the long term intent is to move the data from the remote servers to the local server. Then we can rewrite them without EXEC, and then this slight of hand will no longer be necessary. – Mat Dec 08 '17 at 17:50
  • 1
    It is true that with local data you can get an accurate rowcount with judicious placement of SET NOCOUNT ON and SET NOCOUNT OFF. Also note that with ExecuteReader you can get all the resultsets, and the SqlDataReader.RecordsAffected will give you the sum of the rowcounts up to the current resultset. So it's a higher-resolution API. – David Browne - Microsoft Dec 08 '17 at 17:59

0 Answers0