之所以有这个题目,我既不是故意吸引眼球,也不想在本文对存储过程进行教科书般论述。

最近项目中遇到的存储过程问题,让我想起了去年出差时一位同事的发问:我觉得存储过程挺好用的,为什么你不建议用?

当时我好似胸有万言,但终究没用一个实在的例子回答同事,只是从结论上大侃一通,代码相对于 SQL,复用、扩展、通用性都要更强。想必同事并不信服。

现在想来,我最近正碰到的问题,算是一个可以回答同事的例子吧。

最近项目中有个新需求,需要校验一个用户是否有 Job,Certification,Disclosure 这三个业务数据。

翻看了代码发现,系统的用户个人页面的 C# 代码调用了三个存储过程,去抓取用户的 Job,Certification,Disclosure 数据。

我的新需求,自然需要复用这三个存储过程,否则:若每一处都写一次抓取数据的业务逻辑代码,若业务逻辑发生变化,难以追查和维护所有读取 Job,Certification,Disclosure 的 SQL。

如果我在 C# 代码中调用这已有的三个存储过程,事情本该非常快就能结束。我也是这么做的。

但 code reviewer 认为,我的需求中,并不需要 Job,Certification,Disclosure 这三个业务对象的数据。

我只是需要给定用户是否有 Job,Certification,Disclosure 而已。

所以我应将是否有无 Job,Certification,Disclosure 的判断逻辑写在数据库,最终通过网络从数据库传到 web 服务器的仅是 true 或 false,节省网络流量,这样最好不过了。

也对。除开网络性能,从接口设计的角度讲,接口的传入和返回值,都应是你本身需要的数据,不应带有大量不需要或者需要 caller 去预处理的数据。

从接口语义表达就可知调用的目的,这样代码可读性也会有大大提高。那就动手改。但没想到的是问题来了。

为了讲述问题,我简化代码,假设系统现有的存储过程如下:

  1. CREATE PROCEDURE [dbo].[GetJobs] 

  2.     @PersonId int

  3.     @OrganizaitionId int 

  4.  ) 

  5. AS 

  6. BEGIN 

  7.   SELECT JobId,JobName,JobType FROM Job WHERE PersonId = @PersonId AND OrganizaitionId = @OrganizaitionId 

  8. END 

我在新的存储过程中调用它,我需要获得该 person 的 jobs 的数量,即 GetJobs 返回结果集的 count。

为了实现这一目的,首先想到的是使用临时表,将返回结果集存入临时表,再对其进行 count(*) 的计数操作:

  1. CREATE PROCEDURE [dbo].[MyProc] 

  2.     @PersonId int

  3.     @OrganizaitionId int

  4.  ) 

  5. AS 

  6. BEGIN 

  7.   CREATE TABLE #Temp

  8.     PersonId int

  9.     OrganizaitionId int 

  10.   ) 

  11.  

  12.   INSERT INTO #Temp EXEC dbo.GetJobs 

  13.     @PersonId = @PersonId, 

  14.     @ParentOrgId = @ParentOrgId 

  15.  

  16.   SELECT COUNT(*) FROM #Temp 

  17. END 

这种办法简单有效,但它存在严重的维护问题。未来如果被调用的存储过程的返回结果集字段有变动,那么 MyProc 中的临时表结构也需要随之变化。这是令人难以接受的。

那么将 MyProc 中的 INSERT INTO 换为 SELECT INTO 呢?很遗憾,答案是不行。SQL 本身并不支持这种用法。

给现有存储过程 GetJobs 加 output 参数?本例中因为 GetJobs 已被其他多处代码或 SQL scripts 调用,所以对现有现有存储过程进行改动会有不小风险。

我搜遍网络,一位 MS MVP 的大神的文章几乎总结了所有存储过程之间传递数据的方法:How to Share Data between Stored Procedures。

他在文章中也无可奈何地说道:

Keep in mind that compared to languages such as C# and Java, Transact-SQL is poorly equipped for code reuse, why solutions in T?SQL to reuse code are clumsier.

最终我没能找到一种满意的办法,无奈之下我在新写的存储过程中将查询 Jobs 的语句写一了次。

存储过程在很多场景时有其优势,比如性能,但对于业务逻辑的通用方法,非常不推荐将其写在存储过程中,代码复用、扩展与客户端语言比,相差甚远。

也许终究能实现,但代价与风险比客户端语言要高,得不偿失。天知道还有没有机会和那位前同事再讨论这一话题呢。

作者:杨洋的围脖啊

编辑:陶家龙

出处:segmentfault.com/a/1190000011138993