August 6, 2015 at 1:27 pm
CREATE TABLE #t (a int);
GO
CREATE PROCEDURE dotest
AS
UPDATE #t SET a = 1 WHERE 1 = 0;
RETURN @@ERROR;
GO
EXEC dotest;
SELECT @@ROWCOUNT;
If you run the above, there is a single result set with a 1 for rowcount. In the messages pane, the display is:
(0 row(s) affected)
(1 row(s) affected)
The zero rows are for the procedure that ran the update and the one row is for the selection of @@ROWCOUNT (if you remove the selection of rowcount, just the zero rows is displayed).
I know that @@ROWCOUNT is 1 and not 0 because of the RETURN @@ERROR line. But I am required to have that line in the procedure per code standards.
I'm writing T-SQL unit tests in Visual Studio (also a requirement).
Obviously the zero rows affected value is available. SQL Server knows that zero rows were updated. However, that information is not in @@ROWCOUNT. Where is it? I need to get it in T-SQL so the test can verify that it's zero. I cannot modify the procedure to make the unit test work. I can change any T-SQL that is not in the procedure itself.
Note that the caller of the procedure (in .NET), correctly gets the zero for rows updated. So the production code works, just not the unit test.
Does anyone know where to get the message that zero rows were updated in T-SQL?
August 6, 2015 at 1:36 pm
OK, I answered my own question. My question is similar to this one: http://stackoverflow.com/questions/1195324/using-the-result-of-the-sql-messages-pane
The client gets the message but T-SQL does not. Any decent unit test framework for SQL should allow assertions against the messages returned, but, as I said, I am using Visual Studio 2013 for unit testing T-SQL.
Short answer: The unit test framework is too limited to confirm this expected behavior.
August 6, 2015 at 1:37 pm
Stephanie Giovannini (8/6/2015)
CREATE TABLE #t (a int);GO
CREATE PROCEDURE dotest
AS
UPDATE #t SET a = 1 WHERE 1 = 0;
RETURN @@ERROR;
GO
EXEC dotest;
SELECT @@ROWCOUNT;
If you run the above, there is a single result set with a 1 for rowcount. In the messages pane, the display is:
(0 row(s) affected)
(1 row(s) affected)
The zero rows are for the procedure that ran the update and the one row is for the selection of @@ROWCOUNT (if you remove the selection of rowcount, just the zero rows is displayed).
I know that @@ROWCOUNT is 1 and not 0 because of the RETURN @@ERROR line. But I am required to have that line in the procedure per code standards.
I'm writing T-SQL unit tests in Visual Studio (also a requirement).
Obviously the zero rows affected value is available. SQL Server knows that zero rows were updated. However, that information is not in @@ROWCOUNT. Where is it? I need to get it in T-SQL so the test can verify that it's zero. I cannot modify the procedure to make the unit test work. I can change any T-SQL that is not in the procedure itself.
Note that the caller of the procedure (in .NET), correctly gets the zero for rows updated. So the production code works, just not the unit test.
Does anyone know where to get the message that zero rows were updated in T-SQL?
Yes, you need to capture and return the rows affected in the procedure, like this:
CREATE TABLE #t (a int);
GO
CREATE PROCEDURE dotest
@RowCnt int OUTPUT
AS
UPDATE #t SET a = 1 WHERE 1 = 0;
select @RowCnt = @@ROWCOUNT;
RETURN @@ERROR;
GO
DECLARE @RowsAffected int;
EXEC dotest @RowCnt = @RowsAffected OUTPUT;
SELECT @@ROWCOUNT, @RowsAffected;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply