Template to auto generate SQL Unit Test SP

[Bhavin Katrodiya]

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]

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]

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]

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