I'm using tSqlt to unit test a stored procedure. This stored proc joins to a table-valued function, the function takes no parameters and the results are filtered via the join on clause.
I'm writing multiple tests for the stored proc. Is there a way to to fake the function in such a way that I could return different results based on the test that is being run.
The only solution I can think of is to create a fake per test, which is possible but a little more than clunky.
I imagine an ideal solution would be some sort of variable exposed in tsqlt that would allow me to determine which test I'm in and use some sort of case statement or something.
I use following procedure for that. It is not ideal, but working:
CREATE PROCEDURE [tSQLt].[FakeFunction2]
@FunctionName VARCHAR(200)
, @SchemaName VARCHAR(200) = 'dbo'
, @tmpTableName VARCHAR(200)
AS
BEGIN
DECLARE @Params VARCHAR(2000);
DECLARE @NewName VARCHAR(MAX) = @FunctionName + REPLACE(CAST(NEWID() AS VARCHAR(100)), '-', '');
DECLARE @FunctionNameWithSchema VARCHAR(MAX) = @SchemaName + '.' + @FunctionName;
DECLARE @RenameCmd VARCHAR(MAX) = 'EXEC sp_rename ''' + @FunctionNameWithSchema + ''', ''' + @NewName + ''';';
DECLARE @newTbleName VARCHAR(200) = @SchemaName + '.tmp' + REPLACE(CAST(NEWID() AS VARCHAR(100)), '-', '');
DECLARE @newTblStmt VARCHAR(2000) = 'SELECT * INTO ' + @newTbleName + ' FROM ' + @tmpTableName;
EXEC tSQLt.SuppressOutput @command = @newTblStmt;
SELECT @Params = p.params
FROM
( SELECT DISTINCT ( SELECT p1.name + ' ' + type1.name + b.brk + ',' AS [text()]
FROM sys.types type1
JOIN sys.parameters p1 ON p1.system_type_id = type1.system_type_id
CROSS APPLY
( SELECT CASE WHEN type1.name LIKE '%char' OR type1.name = 'varbinary' THEN
REPLACE(
'(' + CAST(p1.max_length AS VARCHAR(5)) + ')', '-1', 'MAX')
WHEN type1.name IN ('decimal', 'numeric') THEN
'(' + CAST(p1.precision AS VARCHAR(5)) + ', '
+ CAST(p1.scale AS VARCHAR(5)) + ')'
WHEN type1.name IN ('datetime2') THEN
'(' + CAST(p1.scale AS VARCHAR(5)) + ')'
ELSE ''
END AS brk) b
WHERE p1.object_id = p.object_id
ORDER BY p1.parameter_id
FOR XML PATH('')) [parameters]
FROM sys.objects AS o
LEFT JOIN sys.parameters AS p ON p.object_id = o.object_id
LEFT JOIN sys.types AS t ON t.system_type_id = p.system_type_id
WHERE o.name = @FunctionName AND o.schema_id = SCHEMA_ID(@SchemaName)) [Main]
CROSS APPLY
(SELECT LEFT(Main.[parameters], LEN(Main.[parameters]) - 1) params) AS p;
EXEC tSQLt.SuppressOutput @command = @RenameCmd;
DECLARE @newFunctionStmt VARCHAR(MAX) = '';
SET @newFunctionStmt = 'CREATE FUNCTION [' + @SchemaName + '].[' + @FunctionName + '](' + COALESCE(@Params,'') + ')';
SET @newFunctionStmt = @newFunctionStmt + ' RETURNS TABLE AS RETURN (SELECT * FROM ' + @newTbleName + ');';
EXEC tSQLt.SuppressOutput @command = @newFunctionStmt;
END;
and usage:
INSERT INTO #table
(col1
, col2
, col3)
VALUES
('a', 'b', 'c'),
('d', 'e', 'f');
EXEC tSQLt.FakeFunction2 @FunctionName = 'function_name'
, @SchemaName = 'dbo'
, @tmpTableName = '#table';
now with any passed parameter to that function it will always return the values from #table
temp table