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?
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#.