1

From C# I launch an update query using (a stored procedure):

command.ExecuteNonQuery(...);

Then in the stored procedure, I build a dynamic query and execute using sp_executesql:

    DECLARE @ParmDefinition NVARCHAR(500)
    DECLARE @pParam1 NUMERIC(18,0)
    DECLARE @pParam2 BIGINT

    SET @ParmDefinition = N'@Param1 numeric(18,0), @Param2 bigint'
    SET @pParam1 = @Param1
    SET @pParam2 = @Param2

    DECLARE @SQLString  nvarchar(max)= 'UPDATE ' +  @MyServer + @MyDB + '.[dbo].[MyTable] ' +
                                       'SET SomeField= @Param1 ' +
                                       'WHERE F1= @Param2', @rowcnt INT;

    EXECUTE sp_executesql @SQLString, @ParmDefinition, @Param1 = @pParam1, @Param2 = @pParam2
    SELECT @rowcnt = @@ROWCOUNT

In C# ExecuteNonQuery is returning -1. I want to get the number of rows affected.

What's the problem?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Willy
  • 9,848
  • 22
  • 141
  • 284
  • Please, post the C# code that call the SP.... as i understand your question, @rowcnt must be defined as an output parameter. – MLeblanc Nov 21 '18 at 18:32
  • I have already solved. The problem was that at the beginning of the stored procedure I had put below line: SET NOCOUNT ON. By removing this line, now is working, the number of rows affected is working. Thx. – Willy Nov 21 '18 at 18:43

1 Answers1

2

I have solved it.

The culprit was the line I had put at the beginning of the stored procedure:

SET NOCOUNT ON;

By removing above line, now it is working perfectly, the number of rows affected is returned correctly to C#.

Willy
  • 9,848
  • 22
  • 141
  • 284