August 23, 2016 at 12:30 pm
I have some sprocs in SS2012 and I want to unit test them. I haven't unit tested sprocs before. Based on a quick Google, it seems that I should be able to right-click a sproc and select "Create Unit Tests" from the context menu. However, this option is not available in my SSMS instance. Are sproc unit tests not natively supported in SSMS 2012 or is there some type of additional software that I need to download and install?
August 23, 2016 at 12:56 pm
It sounds like you're describing some specific implementation of unit testing within SSDT or Visual Studio.
Unit Testing a stored procedure, generally speaking, is when you execute a stored procedure using a set of input parameters and then confirm that the output or sequence of DML operations match a documented result. This can be done manually though a SSMS query window or using a special unit testing tool.
For example, here I'm unit testing a procedure called usp_CityPopulation_List, confirming the result contains the top 5 populated cities in the US ranked in descending order by population size.
EXEC usp_CityPopulation_List @country = 'US', @toprank = 5;
New York 8,550,405
Los Angeles 3,971,883
Chicago 2,720,546
Houston 2,296,224
Philadelphia 1,567,442
I typically include a few unit tests within the header comments of my stored procedures.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 23, 2016 at 4:19 pm
For unit testing within SQL Server, I'd suggest looking at T-SQLT[/url]. It's unit testing for SQL Server and open source. If you want to automate the process and have reports, etc., you can take a look at SQL Test [/url]from my employer, Redgate Software.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 23, 2016 at 8:25 pm
Eric M Russell (8/23/2016)
It sounds like you're describing some specific implementation of unit testing within SSDT or Visual Studio.Unit Testing a stored procedure, generally speaking, is when you execute a stored procedure using a set of input parameters and then confirm that the output or sequence of DML operations match a documented result. This can be done manually though a SSMS query window or using a special unit testing tool.
For example, here I'm unit testing a procedure called usp_CityPopulation_List, confirming the result contains the top 5 populated cities in the US ranked in descending order by population size.
EXEC usp_CityPopulation_List @country = 'US', @toprank = 5;
New York 8,550,405
Los Angeles 3,971,883
Chicago 2,720,546
Houston 2,296,224
Philadelphia 1,567,442
I typically include a few unit tests within the header comments of my stored procedures.
I'm with you, my unit test for SPs has always been a manual thing for me.
I'll simply execute the Stored proc. with different parameter inputs if there are and based on the result set its right or wrong.
Once I'm happy a tester will come in and do it from the Application itself. any issues they will then do the same thing on the SP to determine where the problem is caused.
August 24, 2016 at 12:14 am
Hi all,
Can somebody explain if there are any tools available to unit test the functional test cases in database?
Thanks,
Durga Prasad.
August 24, 2016 at 6:34 am
durga.palepu (8/24/2016)
Hi all,Can somebody explain if there are any tools available to unit test the functional test cases in database?
Thanks,
Durga Prasad.
Yes. See my post above. T-SQLT and SQL Test [/url]from Redgate Software.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 24, 2016 at 7:07 am
@Grant Fritchey,
Thanks for the reply.
It seems the SQL Test tool will unit test to check the values of data types and to mock database objects but my requirement is not only data types validation but also functionality testing i.e. business validations
Thanks,
Durga Prasad.
August 24, 2016 at 7:36 am
It will do the business validations, absolutely. You just have to write the tests. There's not a tool out there that knows your business well enough to automatically generate tests for the business.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 24, 2016 at 11:43 am
Grant Fritchey (8/24/2016)
It will do the business validations, absolutely. You just have to write the tests. There's not a tool out there that knows your business well enough to automatically generate tests for the business.
Yep - you actually have to know your database app and data and data access patterns and write code (i.e. unit tests using tsqlt.org's awesomeness) to validate whatever you want validated.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 24, 2016 at 11:55 am
Grant Fritchey (8/24/2016)
It will do the business validations, absolutely. You just have to write the tests. There's not a tool out there that knows your business well enough to automatically generate tests for the business.
For what it's worth, one method of unit testing (or smoke testing) is to run and event profiler trace while QA tests some discrete set of features within the application, and then the saved trace can later be replayed to execute the stored procedure calls and statements in proper sequence using identical parameters. Still, the developer or QA replaying the trace must know what to look for in the error logs or data.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply