0

I have the following SQL

BEGIN
DECLARE @UpdateCount int
    EXEC (@UpdateSQL)
SET @UpdateCount = @@RowCount


IF (@UpdateCount < 1)
BEGIN
    EXEC (@InsertSQL)
END
END
SET NOCOUNT ON;

This lives inside a trigger however it isn't working as I would expect, it updates but doesn't insert, I don't know what I'm missing..

The SQL inside @InsertSQL is fine as well, I've tested this.

Alec.
  • 5,371
  • 5
  • 34
  • 69
  • Maybe using the `MERGE` statement could be a solution? https://msdn.microsoft.com/en-us/library/bb510625.aspx – jpw Feb 23 '15 at 15:44

1 Answers1

0

According to the documentation, EXECUTE statements do not affect the @@ROWCOUNT:

https://technet.microsoft.com/en-us/library/ms187316(v=sql.110).aspx

One option is to use sp_executesql and output parameters:

DECLARE @UpdateCount int
SET @UpdateSQL = @UpdateSQL + '; select @UpdateCount = @@rowcount'
exec sp_executesql @UpdateSQL, N'@UpdateCount int output', @UpdateCount = @UpdateCount output

I'll be honest though, I haven't been able to reproduce your issue in any version of SQL Server that I have, so there must be more to your problem.

Necreaux
  • 9,451
  • 7
  • 26
  • 43
  • That is not correct, it states it preserves rowcount, as opposed to resetting it. – Alec. Feb 23 '15 at 14:58
  • That's what I said. "do not affect"=="preserve" right? The statement in question in the docs is:EXECUTE statements preserve the previous @@ROWCOUNT. – Necreaux Feb 23 '15 at 15:08