• 动态SQL查询语句实例
  • 发布于 2个月前
  • 257 热度
    0 评论
  • 林勇
  • 0 粉丝 38 篇博客
  •   
 一、SQL语句直接处理非数字型列的“累加”问题

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

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

1
2
3
4
5
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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--使用游标
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、单引号,双引号

单引号:

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

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

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

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

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

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

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

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

来看一个简单的SQL语句:

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

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

1
2
3
4
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执行

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

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

1
2
3
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是一样的:

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

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

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

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

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

1
2
3
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中:

1
2
3
4
5
/*将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):

1
2
3
4
5
6
/*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

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

用户评论