Template to auto generate SQL Unit Test SP

15 views
Skip to first unread message

Bhavin Katrodiya

unread,
Oct 12, 2021, 2:33:08 AMOct 12
to tSQLt
Hey Developers,

I am not sure I am looking for more than expected but I am planning to implement unit tests for more than 1K+ SPs for now and many more in the future as well. So try to decrease developer efforts to write unit tests.

Looking for your feedback or any suggestion for below?

So as per concept SQL Unit Test break down into three stages
ARRANGE
ACT
ASSERT

Do you guy's come across any tools or template which helps to auto-generate SQL Unit test SP based on original source code of SP based on all three stages? So it would really help to save coding times.

For Example Sp code look like below  :
CREATE PROCEDURE [dbo].[upt_Descp_by_id]
(
@input_id INT,
@input_descp VARCHAR(100)
)
AS
BEGIN

  UPDATE dbo.testTable 
  SET TestDescp = @input_descp
  WHERE Id = @input_id 
 
  SELECT Id, TestDescp
  FROM dbo.testTable 
  WHERE Id = @input_id 
 
END
GO


So, based on SP source code its auto-generate template\SP for  SQL Unit test like


CREATE PROCEDURE [DemotSQLt].[test upt_activity_title_by_id_city_v1] AS
BEGIN

    --ARRANGE Fake the required table consumed by SP.
    EXEC tSQLt.FakeTable 'dbo.testTable';
 
    -- Insert test data into the table
    INSERT INTO dbo.testTable 
(Id,  TestDescp  ) VALUES
    (< manual  input>)
SELECT Id, TestDescp INTO #actual  FROM dbo.testTable  WHERE 1=0;

-- ACT
DECLARE @input_id_test INT=< manual  input>, @input_descp=< manual  input>

    INSERT INTO #actual
    EXEC [dbo].[upt_Descp_by_id]
@input_id = @input_id_test,
@input_descp = @input_descp
 
    --Create an empty #Expected temp table that has the same structure as the #Actual table
    SELECT * INTO #expected FROM #actual WHERE 1=0;
 
    INSERT INTO #expected    
(Id,  TestDescp  ) VALUES   
(< manual  input>);
    --ASSERT    
EXEC tSQLt.AssertEqualsTable '#expected', '#actual','DONE WITH ERROR ---';
END
GO

So, in the END, action is required to replace values to <manual input> only at the end.

Thanks & Regards,
Bhavin 

Krzysztof Strugiński

unread,
Oct 12, 2021, 2:07:58 PMOct 12
to tSQLt

Hi Bhavin,

Is it the case that a manager has ordered unit tests to be prepared for all procedures? Do you need to achieve KPIs on the code coverage with unit tests to get a bonus?

I suspect that you are not the author of all these 1K+ SP, but just got the assignment to write some unit tests? The Client will have no value from the code coverage of tests if other programmers who add/modify these procedures do not consciously and professionally use TDD, right?

A similar example from the life of a programmer (and many, many more) can be found in Sandro Mancuso's book „The Software Craftsman: Professionalism, Pragmatism, Pride”. I sincerely recommend reading this book.

Greetings from Toruń, Poland.

Krzysztof Strugiński

Bryant McClellan

unread,
Oct 12, 2021, 3:19:56 PMOct 12
to ts...@googlegroups.com
Hi Bhavin,
To give you an idea where I am coming from... We have an internal schema of extensions to tSQLt. We have some test generators for schema, FK and constraint tests. We also have constructed capabilities for detailed testing of system-versioned tables. I manage the base framework and our extensions for all developers here. I have something of an idea of what is required to build test generators.

If you take a black-box approach where you only care about the table shape, you'd need to discover the table shape, figure out constraints (PKs, FKs, NULLability...), generate an appropriate set of test data, fake the table, deal with downstream constraints (like SCHEMABINDING), and probably 5 or 6 other things I am glossing over. This of course assumes that you are doing basic CRUD operations. Then it is a developer choice, going forward, to determine if the test generator is appropriate for the next new stored procedure.

By black-box approach I mean that X input data should yield Y output data. The test generator can have no intimate knowledge of the thing under test. That is how our developers are coached to write tests.

It isn't simple but it is doable because the bulk of what you need to know is in SQL. Of course the limitation, as always for a generator, is that you cover only the general case. If there is such a test generator I haven't run across it.

G Bryant McClellan
Senior Solutions Architect
260.748.5393 (direct)

Do it Best Corp.
6502 Nelson Road | Fort Wayne, IN 46803



--
You received this message because you are subscribed to the Google Groups "tSQLt" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tsqlt+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/418e6e09-d81a-4e46-9e1a-fe0cc3744664n%40googlegroups.com.

Bhavin Katrodiya

unread,
Oct 14, 2021, 3:56:43 AMOct 14
to tSQLt
Hey  Toruń, Poland, Thanks for sharing the book name. & also appreciated for being concerned about other things, however, it's really not related to any ordering from a manager, KPI, Bonus, and authors of SPs. (^^) Trying to save time for me and the Team to writing some repetitive code with minor changes.

Hey G Bryant McClellan, Noted with many thanks for your valuable suggestion about a problem we are discussing in this thread. agree with your thoughts and make sense about test generator challenges. Let me try my best. 

Thanks & Regards,
Bhavin
Reply all
Reply to author
Forward
0 new messages