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

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

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

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

 

1	DECLARE @Result varchar(8000)

2	SET @Result=''

3	SELECT @Result=@Result + CASE WHEN @Result='' THEN FirstName ELSE ',' + FirstName

4	END FROM Person ORDER BY Id

5	SELECT @Result AS Names

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

 

01

--使用游标

02	DECLARE @Result varchar(8000)

03	DECLARE @SQL varchar(50)

04	DECLARE c CURSOR FORWARD_ONLY READ_ONLY FOR

05	SELECT FirstName FROM Person ORDER BY Id

06	SET @Result = ''

07

OPEN c

08	FETCH next FROM c INTO @SQL

09	WHILE @@fetch_status = 0

10

BEGIN

11	SET @Result = CASE WHEN @Result=''

12

THEN @SQL

13	ELSE @Result + ',' + @SQL

14

END

15	FETCH next FROM c INTO @SQL

16

END

17	DEALLOCATE c

18	SELECT @Result AS Names

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

 

二、动态SQL语句基础

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

1、单引号，双引号

单引号：

 

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

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

 

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

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

 

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

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

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

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

来看一个简单的SQL语句：

 

1	DECLARE @a varchar(20),@b varchar(20)

2	SET @a='jeff'

3	SET @b=' wong'

4	PRINT @a+@b

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

 

1	DECLARE @a varchar(20),@b varchar(20)

2	SELECT @a='jeff'

3	SELECT @b=' wong'

4	PRINT @a+@b

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

3、EXEC(@sql)

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

 

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

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

 

1	DECLARE @Sql varchar(2000)

2	SET @Sql='SELECT TOP 10 * FROM Person(NOLOCK) ORDER BY Id DESC'

3	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是一样的：

 

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

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

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

 

1	DECLARE @Sql varchar(2000)

2	SET @Sql='SELECT TOP 10 * FROM Person(NOLOCK) ORDER BY Id DESC'

3	EXEC sp_executesql @Sql

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

 

1	DECLARE @Sql nvarchar(2000)

2	SET @Sql='SELECT TOP 10 * FROM Person(NOLOCK) ORDER BY Id DESC'

3	EXEC sp_executesql @Sql

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

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

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

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

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

 

1	/*将exec执行结果放入变量num中*/

2	DECLARE @Num int, @Sql nvarchar(4000)

3	SET @Sql='SELECT @TotalCount=COUNT(0) FROM Person '

4	EXEC sp_executesql @Sql,N'@TotalCount int output',@Num output

5	SELECT @Num AS TotalCount

6、两个类型转换函数

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

 

1	/*CAST 和 Convert函数*/

2	DECLARE @input int

3	SET @input=1234

4	SELECT CONVERT(varchar(50),@input)+' abc' AS result

5	SET  @input=@input+1000

6	SELECT CAST(@input AS varchar(50))+ ' xyz' AS result

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16436858/viewspace-681072/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16436858/viewspace-681072/