SQL存储过程调试方法

在过去的一年时间里，很大一部分时间花在SQL编程上，刚开始是改别人的，后来越来越烦躁，就重写了很多的代码。当然重写的代码一方面是前面的代码比较换乱，不易阅读，另一方面是新增加的功能接连到来，仿佛往一个袋子里塞东西，慢慢的，就要把袋子塞暴了。

重写代码避免了这些麻烦，可也引入的错误，几经反复，终于稳定下来。我也痛定思痛，把写sql的经验教训总结如下。

 

经验1，模块化。

虽然SQL只是脚本，但是本人强烈推荐将不同的功能划分在不同的存储过程里，再由上层的存储过程来调用它们。每个存储过程只做一件事情，可能生成一个表，可能是修改表的一列值，但不要做两件以上。这样做有以下好处：

1. 更容易找到出错的代码，A表错了，就去生成A表的存储过程找；
2. 更容易重复使用代码，你可能会生成几个相似的表，那么你可以调用一个SP多次，只需用不同参数即可；
3. 更容易扩展代码，增加一个新的SP比在一个SP中添加内容通常要容易很多；
4. 更少的变量，这很重要，少的变量意味着含义更加清楚；

经验2，在适当的时候使用函数，虽然很多时候函数可能用动态SQL来替换，但我还是提倡用函数。Table函数可以返回一个表，可以当成一个数组或map来使用，值函数可以减少sql的复杂性；

 

经验3，使用sql模板。如下所示：

通常的做法：set @SQL='select '+@col+' from '+@tbl+'

较好的方法是：

set @SQL_Template='select $col from $tbl'

set @sql = replace(@SqL_template,'$col',@col)

...

这样做可以维护sql语句的完整性，而不会支离破碎，让人难以阅读。而且@SQL_temp可以重复利用，只需替换不同的变量就可以了。

 

经验4，采用正确的调试方法。这和其他编程语言是一致的，经验如下：

1. 出现错误，一定要仔细的分析问题，不要盲目的猜测，碰运气的修改。
2. 将能重现错误的SQL写出来，单独放在一个窗口里，只需要简单的修改就可以验证一个假设。
3. 在关键的地方加print或select语句，将关键的变量打印出来。
4. 在怀疑可能出错的语句后面加Return，避免陷入复杂的业务逻辑，浪费时间；
5. 使用select top into制作结构相同的小表来验证逻辑，可以减少sql执行的时间。
6. 查找代码中的类似错误，加以修正；

经验5，使用constraint或unique index来尽可能早的发现错误，使用@@error可以用来检查上一个SQL执行的结果，如果不为0就是发生错误，可以立刻返回，以避免进一步的错误。

 如下的语句，在employee的第二列上有unique的index

1.  select * from employee
2. insert employee values(2,50,'x')
3. insert employee values(2,50,'y')
4. select * from employee

显然第三个语句会失败，但这并不会阻止第四个语句的执行。如果在一个复杂数据抽取过程中，一个错误没有及时的制止，那么后面就会引起灾难性的后果。

 

经验6， 不断制作测试脚本，并保存起来。这是很重要的，不要等出错了再去制造脚本。