tSQLt.FakeTable doesnt seem to work with views that have constants/derived fields
Asked Answered
C

4

8
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?

Crinkumcrankum answered 19/2, 2013 at 19:7 Comment(0)
C
17

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
Constitutional answered 19/2, 2013 at 19:19 Comment(1)
This just saved me faking and padding 15 tables!Drumfish
C
3

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]'
Cobble answered 31/7, 2019 at 18:25 Comment(2)
Thank you, Jason for fixing the format of the post.Cobble
Worked perfectly for me and way easier than dynamic SQL. Thank you!Dicast
P
1

As Sebastian said, dynamic SQL is the way to go. If there are a lot of columns (especially text/date types that require single-quotes), I use this pattern:

  1. Create a temp table with the same definition as the view
  2. Populate the temp table
  3. Use dynamic SQL to copy the data from the temp table to the faked view
-- #1
SELECT TOP(0) *
INTO #tempView
FROM dbo.viewWithIssues

-- #2
INSERT INTO #tempView (clock_id, IsTerminated)
VALUES  ('1111', '0'), ('2222', '1'), ('3333', '1')

-- #3
EXEC ('INSERT INTO dbo.viewWithIssues SELECT * FROM #tempView')
Proliferate answered 25/7, 2019 at 15:57 Comment(1)
Awesome, even the temp table could be created in the setup and make lighter the test.Poachy
C
0

I'd suggest you create a stored procedure with the insert statement and call it after the exec tSQLt.FakeTable..

CREATE PROCEDURE    TestChanges.[Test Data]

AS
BEGIN
...
exec tSQLt.FakeTable 'dbo.viewWithIssues', @identity=1, @ComputedColumns=1, @Defaults=1;

execute TestChanges.[Insert into viewWithIssues]

...
END
Calumniation answered 2/7, 2019 at 16:6 Comment(0)

© 2022 - 2025 — McMap. All rights reserved.