How is SQL Change Automation helping you? Share to win DevOps books.

tSQLt test with TVP failing to detect the TVP

catalogdbcatalogdb Posts: 21 New member
edited August 12, 2020 3:23PM in SQL Change Automation
Hi,

I have created a test using SQL test in SSMS and creating migration script in SCA tests project.
Now the issue is the SP which I created tSQLt test for require a TVP (table-valued) parameter this is working from from SSMS but when I try to create migration script the build is failing with a sql server error messag

"Must declare the table variable "@myTVP".
Parameter or variable '@myTVP' has an invalid data type."

I am sure I have added this new SP and TVP in the development SCA project but its not detecting the TVP. Is this a limitation in SCA and any workaround?

Update: As a workaround, I have added CREATE TVP DML inside the test

Code (with fake names)


BEGIN
--Assemble
EXEC tSQLt.FakeTable @TableName = N'dbo.MyTable'
--
INSERT INTO dbo.MyTable (Column1, Column2, Column3, Column4)
VALUES (107, 1, 1, 0), (107, 2, 1, 0), (107, 3, 2, 0), (107, 4, 2, 0), (107, 5, 1, 0), (107, 6, 1, 0)
-- Fake calculation for first 4 records
SELECT Column1, Column2, Column3
, CASE WHEN Column3 < 5 THEN (Column3  * Column4 *2) ELSE Column4 END AS Column4
INTO #Expected
FROM dbo.MyTable
--
--Act
IF TYPE_ID(N'[dbo].[TVP_MyTVP]') IS NULL
CREATE TYPE [dbo].[TVP_MyTVP] AS TABLE
(
[Column1] [int] NOT NULL,
[Column2] [int] NOT NULL,
[Column3] [int] NOT NULL,
[Column4] [decimal] (19, 4) NOT NULL
)
DECLARE @MyTVPTVP TVP_MyTVP
INSERT INTO @MyTVPTVP (Column1, Column2, Column3, Column4)
SELECT Column1, Column2, Column3, Coumn3 * Column4 as  Column4 FROM dbo.MyTable WHERE Coumn3 < 5
--
DECLARE @output INT
EXEC @output = dbo.MySP @MyTVPTVP
--
SELECT Column1, Column2, Column3, Column4
INTO #Actual
FROM dbo.MyTable
--
EXEC tSQLt.AssertEqualsTable @Expected = N'#Expected', -- nvarchar(max)
                             @Actual = N'#Actual',   -- nvarchar(max)
                             @Message = N'Got expected result',  -- nvarchar(max)
                             @FailMsg = N'Unexected results'   -- nvarchar(max)

Thanks in Advance

Answers

Sign In or Register to comment.