tSQLt test with TVP failing to detect the TVP

catalogdb

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

way0utwest

Can you post the code? 

catalogdb

@way0utwest
I posted the test code block with all fake names