exec tSQLt.SetFakeViewOn 'dbo.viewWithIssues';
GO
CREATE PROCEDURE TestChanges.[Test Data]
AS
BEGIN
...
exec tSQLt.FakeTable 'dbo.viewWithIssues', @identity=1, @ComputedColumns=1, @Defaults=1;
INSERT INTO dbo.viewWithIssues (clock_id, IsTerminated)
VALUES ('1111', '0'), ('2222', '1'), ('3333', '1')
...
END
GO
exec tSQLt.SetFakeViewOff 'dbo.viewWithIssues';
GO
Then it goes on to populate #Actual and #Expected after a procedure is called. The error that it returns is: Update or Insert of view or function 'dbo.viewWithIssues' failed because it contains a derived or constant field.
Does anyone know how to get around this?
The problem is that SQL Server catches this at compile time, so the FakeTable gets never executed. That is what SetFakeViewOn was supposed to catch, however it currently does not work reliably. There is no really clean way around this right now other then putting the inserts and updates into dynamic SQL:
CREATE PROCEDURE TestChanges.[Test Data]
AS
BEGIN
...
exec tSQLt.FakeTable 'dbo.viewWithIssues', @identity=1, @ComputedColumns=1, @Defaults=1;
EXEC('INSERT INTO dbo.viewWithIssues (clock_id, IsTerminated)'+
'VALUES (''1111'', ''0''), (''2222'', ''1''), (''3333'', ''1'');');
...
END
With help from some friends I was able to solve this problem! I moved the tSQLt.FakeTable command to a Setup.sql since tSQLt runs it before the other tests in that schema. We discovered this anomaly by running the FakeTable command and the INSERT INTO command in the same sql query. Like this:
EXEC [dbServername].tSQLt.FakeTable @TableName='vw_viewname', @SchemaName='dbo';
INSERT INTO [dbServername].dbo.vw_myView
(Column1, Column2, Column3, Column4, Column5, Column6, Column7)
VALUES ( '100513','C','2018-12-13','2019-03-25','2014-10-27',' ',40.500,'3');
That would cause the error: [TestSchemaName].[Test usp_MyStuff_Get Get my stuff] failed: (Error) Update or insert of view or function 'dbServername.dbo.vw_myView' failed because it contains a derived or constant field.
This worked:
CREATE PROCEDURE [TestSchemaName].[Setup]
AS
BEGIN
IF @@TRANCOUNT > 0
BEGIN
EXEC [dbServername].tSQLt.FakeTable @TableName='vw_viewname', @SchemaName='dbo'
END
ELSE
BEGIN
RAISERROR('Procedure was run without tsqlt.Run. Aborting procedure', 16, 1)
END
END
CREATE PROCEDURE [TestSchemaName].[Test usp_MyStuff_Get Get my stuff]
AS
BEGIN
IF @@TRANCOUNT > 0
BEGIN
--EXEC [dbServername].tSQLt.FakeTable @TableName='vw_viewname', @SchemaName='dbo'
--I moved the line above to the Setup.sql
INSERT INTO [dbServername].dbo.vw_myView
(Column1, Column2, Column3, Column4, Column5, Column6, Column7)
VALUES ( '100513','C','2018-12-13','2019-03-25','2014-10-27',' ',40.500,'3')
END
ELSE
BEGIN
RAISERROR('Procedure was run without tsqlt.Run. Aborting procedure', 16, 1)
END
END
GO
Publish the project and then from SSMS run:
EXEC tsqlt.run '[TestSchemaName].[Test usp_MyStuff_Get Get my stuff]'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With