none
capturing number of rows affected from within sp

    Question

  • We have the following SP in SQL2008 that we use to stored text (VARCHAR(MAX) scraped from an image.  We invoke it from .NET using SqlCommand and determine success or failure by looking at the number of rows affected by SqlCommand.ExecuteNonQuery().   If the number is 0, we assume failure.   Every now and then this invocation in our Windows NT service returns no rows affected but I can manually run the same command and param values in using TSQL/management studio and it clearly works.


    --SP definition

    ALTER    Procedure [dbo].[ORecordFileTextUpdate]
    --Purpose : Updates the file text for all records with a matching batchid/imageid combination 

    --Input Parameters
    @RepoId INT,
    @BatchId char(6),
    @ImageId smallint,
    @FileText NVARCHAR(MAX)

    AS

    DECLARE @OTable NVARCHAR(6)
    DECLARE @ParamDefs NVARCHAR(500)
    DECLARE @SQL NVARCHAR(4000)

    SET @OTable = 'O' + CAST(@RepoId AS VARCHAR(5))
    SET @SQL = 'UPDATE ' + @OTable + ' SET FileText = @FileText WHERE BatchId = @BatchId AND ImageId = @ImageId'
    SET @ParamDefs = N'@FileText NVARCHAR(MAX), @BatchId CHAR(6), @ImageId SMALLINT'

    EXEC SP_EXECUTESQL @SQL, @ParamDefs, @FileText, @BatchId, @ImageId

    --------------------------------------------------------------------------------------------------

    /// <summary>

    /// Executes a given SqlCommand instance.

    /// </summary>        
    /// <returns>Returns the number of rows affected (NOT RETURN_VALUE).</returns>
    /// <remarks>Note, SET NOCOUNT ON will always return 0.</remarks>
    public int ExecuteNonQuery(SqlCommand cmd)
    {
           
            int rc = 0;
            try
           {
                    OpenConnection(cmd.Connection);

                    rc = cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                 throw new SqlCommandException(ex.Message, cmd, ex);
            }

             return rc;
    }

    Is this unreliable expecting SP_EXECUTESQL returning the number of rows affected from the sp to determine success or failure?

    SQL2008

    .NET 4.0


    Thursday, November 29, 2012 4:44 PM

Answers

  • In this case I would return a single result set from SQL and trap it with the ExecuteScalar() call in .NET.

    The SQL, after your insert/update would be:

    SELECT @@RowCount AS Rows_Affected


    • Marked as answer by scott_m Saturday, December 01, 2012 2:22 AM
    Thursday, November 29, 2012 4:51 PM

All replies

  • In this case I would return a single result set from SQL and trap it with the ExecuteScalar() call in .NET.

    The SQL, after your insert/update would be:

    SELECT @@RowCount AS Rows_Affected


    • Marked as answer by scott_m Saturday, December 01, 2012 2:22 AM
    Thursday, November 29, 2012 4:51 PM
  • If your catch block is not executed, then the SP executed successfully. In theory you can return an output parameter with number of rows affected (select @RowsAffected =@@ROWCOUNT) after the UPDATE command.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, November 29, 2012 5:30 PM
    Moderator