扩展SQL的灵活性——静态T_SQL和动态SQL

左手的ㄟ右手 2022-08-05 02:18 203阅读 0赞

其实我觉得这个博客写在基于web的应用程序开发是最好的，因为目前我们很少在客户端实现排序和过滤功能，而且很多开发采用的都是轻量级的两层模型，所以缺少专门处理数据缓冲和过滤业务，有些人会说，我们可以创建存储过程或者嵌套复杂的控制流程块？但是我想说，这些方案只能解决一部分问题，但是它们带来的问题远远比我想解决的问题要多得多。

# 问题 #

出现这个问题是在我们机房合作通过业务逻辑层来拼接字符串来优化D层代码的时候，但是出现这样一个问题：我这里写好了update一张表的所有字段，但是事实上传入的实体值一般仅仅是其中的几个字段，这就需要扩展我们SQL的灵活性。

# 解决过程 #

在合作中我是负责D层的，但是如何解决这个问题我还是无从下手。后来通过查询很久的资料和论坛的提问，大体了解是关于静态SQL和动态SQL的方向。

## 1. 静态T\_SQL解决方法： ##

update card set cardName=isnull(@cardName,cardName),date=isnull(@date,date),Time=isnull(@Time,time),status=isnull(@status,status) 
    where cardID=@cardID

由上面这段代码可以看到只是需要加上一个isnull（）判断。当然方法不只是一个，比如通过控制流的判断，但是控制流判断还是存在很多潜在问题，也可以用case表达式，我觉得这种方法是在优化控制流来达到如果cardname为空时候，这个字段就保持原来数据，不进行改变。

上面的语句问题：可读性差。

可以通过case表达式来优化：

update card set cardName=case when @cardName is null then cardName else @cardName end
    
    ,date=case when @date is null then date else @date end
    
    ,Time=case when @Time is null then Time else @Time end
    
    ,status=case when @status is null then status else @status end
    
    where cardID=@cardID

这样子的使用CASE表达式替代SQL Server中的动态SQL语句。

## 2. 动态SQL解决方法 ##

原理：拼接的SQL语句，在存储过程中。

注意：

![Center][]

所以说一般不建议使用动态SQL。

exec sp_executesql N'update card set cardName=isnull(@cardName,cardName),date=isnull(@date,date),Time=isnull(@Time,Time),status=isnull(@status,status)
     where cardID=@cardID',N'@cardName varchar(100),@date date,@Time time,@status varchar(30),@cardID int','test',null,null,null,'001'

然后exec（sql）就可以了。

# 感悟 #

关于动态SQL知识一是我理解的不多，所以我也不大敢轻易的解读，以后在我学习的过程中我再把我的理解写出来；二是这对我来说是个新知识，需要时间消化和吸收，需要更多的和别人讨论才能决定是否运用动态SQL。

另外，通过这次出现的问题让我重新对sql sever有了理解，我曾经以为我学了视图，触发器，就已经差不多了，但是即使是小小的sql语句都有这么大学问，不过我觉得满有意思的，就是因为丰富，我每天对曾经学过的知识才会有全新的感受。我觉得sql sever不会学完，永远都在学习中，知识也是，永远都在学习中。

[Center]: /images/20220805/2163099057eb44dd9f1e5be3880668dd.png