动态SQL开发基础和经验再总结

写SQL容易，但难精通。

注：本文示例Person表结构请参考拙文的数据表介绍。

一、SQL语句直接处理非数字型列的“累加”问题

查询要求：取出Person表中所有人的FirstName，并以逗号隔开。

可直接通过SELECT @local_variable = expression的形式实现：

DECLARE @Result varchar(8000)
SET @Result=''
SELECT @Result=@Result + CASE WHEN @Result='' THEN FirstName ELSE ',' + FirstName 
END FROM Person ORDER BY Id
SELECT @Result AS Names

我们还有一种比较笨拙的方式进行查询，那就是通过游标：

--使用游标
DECLARE @Result varchar(8000)
DECLARE @SQL varchar(50)
DECLARE c CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT FirstName FROM Person ORDER BY Id
SET @Result = ''
OPEN c
FETCH next FROM c INTO @SQL
WHILE @@fetch_status = 0
BEGIN
   SET @Result = CASE WHEN @Result='' 
   THEN @SQL 
   ELSE @Result + ',' + @SQL 
   END
   FETCH next FROM c INTO @SQL
END
DEALLOCATE c
SELECT @Result AS Names

大部分SQL查询我们都会力争不用游标，因为不用考虑分配和释放游标，可大大节省数据库资源提升效率。

 

二、动态SQL语句基础

在一中，我们已经使用了动态SQL。这里再总结一下经常使用的动态SQL编程基础。

1、单引号，双引号

单引号：

SELECT '''' AS Result --单引号

那么双引号呢？8个单引号么？如果是真的8个单引号一起，实际上返回的是3个单引号。真正的双引号可以”含有“8个单引号，当然必须要像下面这样加起来：

SELECT ''''+'''' AS Result --双引号

但是更直接的写法是下面这样的：

SELECT '''''' AS Result --双引号

没错，就是6个单引号连写。

单引号和双引号也可以搞得这么烦？这也许正是SQL编程不如高级程序语言来得简单直白的地方。

2、定义变量，给变量赋值

来看一个简单的SQL语句：

DECLARE @a varchar(20),@b varchar(20)
SET @a='jeff' 
SET @b=' wong'
PRINT @a+@b

我们通过DECLARE定义变量，通过SET给变量赋值，也可以通过SELECT给变量赋值：

DECLARE @a varchar(20),@b varchar(20)
SELECT @a='jeff' 
SELECT @b=' wong'
PRINT @a+@b

SET和SELECT赋值的区别：据说SELECT 一次性赋值, 比用SET 逐个赋值效率好。

3、EXEC(@sql)

普通的SQL语句，可以直接通过EXEC执行

EXEC ('SELECT TOP 10 * FROM Person(NOLOCK) ORDER BY Id DESC')--两边的括号不可少

也可以通过定义变量，执行变量sql，但是必须加上括号：

DECLARE @Sql varchar(2000)
SET @Sql='SELECT TOP 10 * FROM Person(NOLOCK) ORDER BY Id DESC'
EXEC ( @Sql )

4、Exec sp_executesql

sp_executesql 支持对 Transact-SQL 字符串中指定的任何参数的参数值进行替换，但是 EXECUTE 语句不支持。因此，由 sp_executesql 生成的 Transact-SQL 字符串比由 EXECUTE 语句所生成的更相似。SQL Server 查询优化器可能将来自 sp_executesql 的 Transact-SQL 语句与以前所执行的语句的执行计划相匹配，以节约编译新的执行计划的开销。

对于普通的SQL语句，这个和EXEC直接执行SQL是一样的：

EXEC sp_executesql N'SELECT TOP 10 * FROM Person(NOLOCK) ORDER BY Id DESC' --必须加N

注意，那个大写的N必须加在要执行的sql语句前面，而且那个N也不是白来的，它还有重要的含义！

如果我们执行的sql语句定义成变量的形式，下面的sql是无法执行的：

DECLARE @Sql varchar(2000)
SET @Sql='SELECT TOP 10 * FROM Person(NOLOCK) ORDER BY Id DESC'
EXEC sp_executesql @Sql

搞怪的是，如果将@sql变量类型由varchar改成nvarchar，就可以执行了：

DECLARE @Sql nvarchar(2000)
SET @Sql='SELECT TOP 10 * FROM Person(NOLOCK) ORDER BY Id DESC'
EXEC sp_executesql @Sql

到这里哀乐响起，为什么为什么？偏偏你要折磨我？下面就来解惑吧：

（1）、EXEC sp_executesql N 这个倒贴过来的N表示后面的sql内容是unicode也是对应nvarchar类型；

（2）、将varchar改成nvarchar才能执行，就是为了执行sql时，将所有参数值转换为字符或 unicode 并使其成为 Transact-SQL 字符串的一部分。

5、将EXEC执行结果放入变量中

比如，我们需要查询Person表的所有记录数，可以像下面这样实现将执行结果放入变量@Num中：

/*将exec执行结果放入变量num中*/
DECLARE @Num int, @Sql nvarchar(4000)  
SET @Sql='SELECT @TotalCount=COUNT(0) FROM Person '  
EXEC sp_executesql @Sql,N'@TotalCount int output',@Num output  
SELECT @Num AS TotalCount

6、两个类型转换函数

下面示例将整数（int）转换成字符串（varchar）：

/*CAST 和 Convert函数*/
DECLARE @input int
SET @input=1234
SELECT CONVERT(varchar(50),@input)+' abc' AS result
SET  @input=@input+1000
SELECT CAST(@input AS varchar(50))+ ' xyz' AS result

这两个平时开发估计经常使用，大家应该不陌生。

----------------------------------------------分割线分割线--------------------------------------------

写的很浅薄，反正也不奢望哪年哪月哪天那谁也能在简历上名正言顺地印上如下一段文字：

”xx，yy年开发经验，精通zz数据库。“