Create stub tSQLt tests in SSDT from stored procedures

I have released another tool as part of the ssdt dev pack, what this does is create tSQLt classes and stub tests. If you open a stored procedure in ssdt and then do "Tools--> SSDT Dev Pack--> Create tSQLt Class" or you can create a keyboard mapping in "tools->options->keyboard", what I do on my machine is map "Tools.CreatetSQLtClass" to Ctrl+K Ctrl+S - it will create a schema with the name of your stored procedure and the correct extended property for tSQLt to register it as a test class.

If you then again either do "Tools--> SSDT Dev Pack--> Create tSQLt Test" or as I have, map it to Ctrl+K Ctrl+T - it will create a tSQLt test but with any tables referenced in the procedure added as fake tables, any parameters the procedure requires defined and an exec on the procedure.

This will build test stubs for stored procedures and table valued functions today, there is no reason not to support other things like scalar functions, I just needed those two first.

If you have the old version installed you can just install the latest vsix and it will upgrade it (https://github.com/GoEddie/SSDT-DevPack/tree/master/release). You will need to restart visual studio though 🙁

This doesn't add the reference to tSQLt so you will need to continue to do that as you do it today.

To build the tests I need the compiled dacpac so if you add a procedure or change it make sure you build the project before trying to add the test otherwise you won't get the correct fake tables (or anything at all).

Walkthrough

If you have this code:

create procedure [dbo].[get_customer]( @customer_id int)

as

select * from customer where Id = @customer_id

go

and this project structure:

note you do need a folder to put the tests into, they can't go into the root of the project.

Choosing "Create tSQLt Schema" with the procedure open causes this dialog to pop up:

If you choose the target folder, in this case if I choose "Tests" then it will create sub-folder under that called "get_customer" where the schema will be created. You can change the name of the schema but if you do the tests will not be created in the correct place - I will fix this in a later version or feel free to do it yourself via a pull request. To manually work around this just rename the schema of the tests when they have been created.

If you click on "OK" the new folder and schema will be created:

This is the way I like to structure my tests, I have a folder where I put the schema (class) and all the tests, it seems a logical way for me to group the tests - if someone has a different way then let me know and I will see how easy it is to incorporate it.

If you then go back to the test and choose "Create tSQLt Test" (or use the shortcut you map) then again you will get the similar dialog to the schema, choose the new folder that was created and the test will be created there for you. You can override the default test name then click OK and the new test should pop up in Visual Studio:

The test proc will look something like:

CREATE PROCEDURE [get_customer].[test get_customer does something]

AS

EXECUTE tSQLt.FakeTable 'dbo', 'customer';

DECLARE @customer_id AS INT = 0;

EXECUTE [dbo].[get_customer] @customer_id;

EXECUTE tSQLt.AssertEquals 'TRUE', 'FALSE', N'Error Not Implemented';

Every table referenced will be faked, there are probably other things we should fake but this feel like a good start to help with some of the overhead that tSQLt tests have.

I hope this is useful and I intend to keep adding to this so if you have any ideas or bugs let me know!

If anyone wants any help with testing or SSDT in general feel free to give me a shout!