I’m developing a stored procedure that will present, for every employee, the time he/she worked, a time keeper.
Because the HR department, presented a lot of rules, complicating very much the logic behind it, I started to develop also some testes with tSQLt.
Here is one test :
ALTER PROCEDURE [TestTimeKeeping].[Test Entrace Without Exit Then Make it 8h]
AS
BEGIN
SET XACT_ABORT ON;
SET NOCOUNT ON;
IF OBJECT_ID('actual') IS NOT NULL DROP TABLE actual;
IF OBJECT_ID('expected') IS NOT NULL DROP TABLE expected;
EXEC tSQLt.FakeTable 'dbo', 'Events';
BEGIN TRY
INSERT INTO dbo.Events ( UserNo, DT, Location)
VALUES ( 1, cast('20191111 08:00:00' as datetime), 39);
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE();
END CATCH
CREATE TABLE Actual (
Department varchar(50),
Marca varchar(13),
Employee varchar(63),
Occupation varchar(50),
IntrareDT datetime,
TotalHours time(0) null,
NormalHours time(0) null,
SDHours time(0) null,
NightHours time(0) null
);
INSERT INTO Actual (Department,Marca,Employee, Occupation, IntrareDT, TotalHours, NormalHours,SDHours,NightHours)
EXEC dbo.Company_Presence_GET @Departaments = NULL, @dataStart='20191101', @dataEnd = '20191125', @Marca = NULL;
CREATE TABLE expected (
Department varchar(50),
Marca varchar(13),
Employee varchar(63),
Occupation varchar(50),
IntrareDT datetime,
TotalHours time(0) null,
NormalHours time(0) null,
SDHours time(0) null,
NightHours time(0) null
);
INSERT INTO expected (Department, Marca, Employee, Occupation, IntrareDT,TotalHours,NormalHours,SDHours,NightHours)
VALUES('DEPARTAMENT IT','3313','First Last Name','Ocup IT','20191111 00:00:00','08:00:00','08:00:00',NULL,NULL);
/*select * from Actual;*/
EXEC tSQLt.AssertEqualsTable 'expected', 'actual';
END;
Sql Server 