SQL Server中存储过程的单元测试

I want to implement Test First Development in a project that will be implemented only using stored procedures and function in SQL Server.

我想在一个只使用SQL Server中的存储过程和函数实现的项目中实现Test First开发。

There is a way to simplify the implementation of unit tests for the stored procedures and functions? If not, what is the best strategic to create those unit tests?

有没有一种方法可以简化存储过程和函数的单元测试的实现?如果没有，创建这些单元测试的最佳策略是什么?

3 个解决方案

#1

It's certainly possible to do xUnit style SQL unit testing and TDD for database development - I've been doing it that way for the last 4 years. There are a number of popular T-SQL based test frameworks, such as tsqlunit. Red Gate also have a product in this area that I've briefly looked at.

当然，可以使用xUnit风格的SQL单元测试和TDD进行数据库开发——在过去的4年里，我一直这样做。有许多流行的基于T-SQL的测试框架，比如tsqlunit。Red Gate在这个领域也有一个产品，我简单地看过。

Then of course you have the option to write your tests in another language, such as C#, and use NUnit to invoke them, but that's entering the realm of integration rather than unit tests and are better for validating the interaction between your back-end and your SQL public interface.

当然，您还可以选择使用另一种语言(如c#)编写测试，并使用NUnit来调用它们，但这是进入了集成领域，而不是单元测试，这对于验证后端和SQL公共接口之间的交互效果更好。

http://sourceforge.net/apps/trac/tsqlunit/

http://tsqlt.org/

Perhaps I can be so bold as to point you towards the manual for my own free (100% T-SQL) SQL Server unit testing framework - SS-Unit - as that provides some idea of how you can write unit tests, even if you don't intend on using it:-

也许我可以大胆地为您指出我自己的免费(100% T-SQL) SQL Server单元测试框架- SS-Unit -因为这提供了一些关于如何编写单元测试的想法，即使您不打算使用它:-

http://www.chrisoldwood.com/sql.htm

http://www.chrisoldwood.com/sql/ss-unit/manual/SS-Unit.html

I also gave a presentation to the ACCU a few years ago on how to unit test T-SQL code, and the slides for that are also available with some examples of how you can write unit tests either before or after.

几年前，我还向ACCU介绍了如何对T-SQL代码进行单元测试，以及一些关于如何在之前或之后编写单元测试的示例。

http://www.chrisoldwood.com/articles.htm

Here is a blog post based around my database TDD talk at the ACCU conference a couple of years ago that collates a few relevant posts (all mine, sadly) around this way of developing a database API.

这是一篇基于我几年前在ACCU会议上的数据库TDD谈话的博客文章，它整理了一些相关的文章(遗憾的是，都是我的)，围绕着开发数据库API的方式。

http://chrisoldwood.blogspot.co.uk/2012/05/my-accu-conference-session-database.html

(That seems like a fairly gratuitous amount of navel gazing. It's not meant to be, it's just that I have a number of links to bits and pieces that I think are relevant. I'll happily delete the answer if it violates the SO rules)

(这似乎是一种相当多余的对肚脐的凝视。这并不是我想要的，只是我有一些我认为相关的链接。如果答案违反了SO规则，我很乐意删除)

#2

It is doable. Create tests and in the setup create a new instance of db and give it some data and then execute the procs. Validate your assumptions, like I got the correct data back. Drop the test db then do it all again in the next test.

它是可行的。创建测试，在设置中创建一个新的db实例，给它一些数据，然后执行procs。验证您的假设，比如我得到了正确的数据。删除测试db，然后在下一次测试中再做一次。

#3

Unit testing in database is actually big topic,and there is a lot of different ways to do it.I The simplest way of doing it is to write you own test like this:

数据库中的单元测试实际上是一个大主题，有很多不同的方法可以实现它。我最简单的方法就是这样写你自己的测试:

BEGIN TRY
<statement to test>
THROW 50000,'No error raised',16;
END TRY
BEGIN CATCH
if ERROR_MESSAGE() not like '%<constraint being violated>%'
THROW 50000,'<Description of Operation> Failed',16;
END CATCH

In this way you can implement different kind of data tests: - CHECK constraint,foreign key constraint tests,uniqueness tests and so on...

通过这种方式，您可以实现不同类型的数据测试:-检查约束、外键约束测试、惟一性测试等等。

#1