翻译：动态SQL与静态SQL－安全（第二部分）-优快云博客

原文地址：http://www.sqlservercentral.com/articles/Performance+Tuning/dynamicvsstatic2/623/
在前一篇文章：动态SQL与静态SQL的第一部分中，我简要地介绍了有关使用动态SQL涉及到的安全问题。在这篇文章我打算介绍一些方法，既使有静态SQL的解决方法，但是还存在诱使你使用动态SQL的情况。

许多人之所以选择使用动态SQL，其原因在于开发和部署不需要太多的考虑，代码少。换言之，相对于静态SQL来说，动态SQL不需要太多的维护。本文将就上述原因一一验证其正确性。

和先前介绍的文章一样，首先举一些动态SQL的代码片断，然后介绍使用静态SQL完成同样的功能的方法，以及静态SQL实在不能使用时，动态SQL才迫以使用的情况。

注意：示例采用Pubs数据库

案例1：搜索条件

第一种情况就是我见到过最多的、也是说明动态SQL为什么说它需要较少的考虑和实现代码的地方。这里使用Pubs数据库的authors表。我想实现的是根据传入一个参数变量来搜索满足条件的某一个author的信息。下面在查询分析器中要执行的代码：

DECLARE @names varchar(7000)
SET @names = 'white'

SELECT *
FROM authors
WHERE au_lname IN (@names)

假如你想搜索多个名字，就需要修改上面的SET语句，如下所示的：
SET @names = '''white'',''green'''

这里使用了单引号来区分多个名字，然而在执行重新执行上述代码时，则返回空记录。其原因主要是SQL Server将其认为是一个长字符串，要找满足'while','green'。由于我们不能够限制使用名字的数目，唯一能接受的是使一个变量满足我们的要求。最简单的解决方法就是将上述的整个SELECT语句转换为动态SQL，其转换后的代码如下：

DECLARE @names varchar(7000), @Query varchar(8000)
SET @names = '''white'',''green'''
SET @Query = '
SELECT *
FROM authors
WHERE au_lname IN (' + @names + ')'
PRINT @Query
EXEC (@Query)

只要names变量正确格式化执行上述语句就能如期地得到记录行。可是，此种方案需要授权authors表的SELECT权限。下面采用静态SQL来完成上述的功能：

DECLARE @names varchar(7000), @len int, @CurPos int, @PrevPos int

CREATE TABLE #names (names varchar(35))

SET NOCOUNT ON

SET @names = 'white,green'
SET @len = LEN(@names) + 1
SET @CurPos = 1
SET @PrevPos = @CurPos

WHILE @CurPos < @len + 1
BEGIN
     IF SUBSTRING(@names + ',', @CurPos, 1) = ','
     BEGIN
           INSERT INTO #names (names)
           SELECT SUBSTRING(@names,@PrevPos,@CurPos - @PrevPos)
           SET @PrevPos = @CurPos + 1
     END
     SET @CurPos = @CurPos + 1
END

SET NOCOUNT OFF

SELECT *
FROM authors
WHERE au_lname IN (SELECT * FROM #names)
DROP TABLE #names

使用静态SQL方法另一个好处就是仅需要修改保存名字的变量即可。对于采用动态SQL，由于VARCHAR变量的字符受限（最大为8000个字符），不可能用于保存名字的变量耗尽8000个字符，同时也需要为其执行@Query代码留出空间。

通过比较上述两个方法，不难发现静态SQL代码要比动态SQL要多，另外，静态SQL需要花费较长时间来开发和测试。

案例2：排序列

假如我们想要实现根据指定的变量来实现对数据的排序，在学习如何在ORDER BY中使用CASE函数之前，通常想到实现此功能的是使用动态SQL，如下代码所示：

DECLARE @Query varchar(500), @OrderBy varchar(10), @Sequence varchar(4)
SET @OrderBy = 'au_lname'
SET @Sequence = 'DESC'

SET @Query = '
            SELECT *
            FROM authors
            ORDER BY ' + @OrderBy + ' ' + @Sequence

IF @OrderBy = 'au_lname' SET @Query = @Query + ', au_fname ' + @Sequence
PRINT @Query

EXEC (@Query)

而采用静态SQL则需要较多的代码，如下所示：

DECLARE @OrderBy varchar(10), @Sequence varchar(4)

SET @OrderBy = 'au_lname'
SET @Sequence = 'DESC'

SELECT *
FROM authors
ORDER BY CASE @OrderBy + @Sequence
                                  WHEN 'au_lnameASC' THEN au_lname
                                  WHEN 'au_fnameASC' THEN au_fname
                     END ASC,
                     CASE @OrderBy + @Sequence
                                 WHEN 'au_lnameDESC' THEN au_lname
                                 WHEN 'au_fnameDESC' THEN au_fname
                     END DESC,
                     CASE @OrderBy + @Sequence
                                  WHEN 'au_lnameASC' THEN au_fname
                     END ASC,
                     CASE @OrderBy + @Sequence
                                  WHEN 'au_lnameDESC' THEN au_fname
                     END DESC

由此看出，静态SQL需要较多的代码来实现。但是，当在存储过程中执行时，用户只需要有执行权限即可，而动态SQL执行时则会失败，原因是用户在authors表无SELECT权限。

案例3：联接(Join)

假如我们想要显示author或title的信息，仅当传入值给@Author时显示author信息，传入值给@Title时显示title信息，同时传入值给@Author和@Title变量时则显示author和title信息;在为这两个变量赋值时，我们需要3张表中返回结果，若仅传入一个变量值或仅需要一张表时，以下是动态SQL的解决方法：

DECLARE @Query varchar(500), @Author varchar(20), @Title varchar(20)
SET @Author = ''
SET @Title = 'e'
SET @Query = 'SELECT * FROM '

IF @Author <> ''
SET @Query = @Query + 'authors a'

IF @Author <> '' AND @Title <> ''
SET @Query = @Query + CHAR(13) +
'INNER JOIN titleauthor ta ON ta.au_id = a.au_id' + CHAR(13) + 'INNER JOIN '

IF @Title <> ''
SET @Query = @Query + 'titles t'

IF @Author <> '' AND @Title <> ''
SET @Query = @Query + ' ON t.title_id = ta.title_id'

IF @Author <> '' OR @Title <> ''
SET @Query = @Query + CHAR(13) + 'WHERE '

IF @Author <> ''
SET @Query = @Query + 'au_lname LIKE ''%' + @Author + '%'''

IF @Author <> '' AND @Title <> ''
SET @Query = @Query + CHAR(13) + 'AND '

IF @Title <> ''
SET @Query = @Query + 'title LIKE ''%' + @Title + '%'''

PRINT @Query
EXEC (@Query)

下面是使用静态SQL方法实现同样的功能：

DECLARE @Author varchar(20), @Title varchar(20)

SET @Author = ''
SET @Title = 'e'

IF @Author <> '' AND @Title = ''
BEGIN
     SELECT *
     FROM authors
     WHERE au_lname LIKE '%' + @Author + '%'
END

IF @Title <> '' AND @Author = ''
BEGIN
      SELECT *
      FROM titles
      WHERE title LIKE '%' + @Title + '%'
END

IF @Title <> '' AND @Author <> ''
BEGIN
      SELECT *
      FROM authors a
         INNER JOIN titleauthor ta
                 ON ta.au_id = a.au_id
            INNER JOIN titles t
                 ON t.title_id = ta.title_id
      WHERE au_lname LIKE '%' + @Author + '%'
            AND title LIKE '%' + @Title + '%'
END

对于以上两种解决方案，必须经过测试方保保证返回的相同的结果集，另外需要注意的是在采用动态SQL方案时，若没有更正使用单引号问题，则会破坏查询的执行，未授权代码将得以执行。

静态SQL易于维护

     只有不为用户授权对整个数据库的读写权限时，静态SQL才变得易于维护，原因是动态SQL需要为其执行时引用的每一张表或对象进行必要的授权，而静态SQL只需授权存储过程的执行权限即可。使用静态SQL的另外一个优势就是SQL Server可以帮你检查SQL语法的有效性，而动态SQL来说，SQL Server只有在运行时才可以判断其语法的有效性。
    随着动态SQL代码的增加，编写的查询也变得更为复杂，代码可读性也较差，另外执行查询的变量也可能会受到8000个字符串的限制（虽然在SQL Server 2005中为VARCHAR(MAX)），当将动态SQL转换为静态SQL的时间也会翻倍增加。

小结：
    当编写代码时应该尽量考虑使用静态SQL，仅在找不到使用静态SQL的方法时才考虑动态SQL。

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

转载于:http://blog.itpub.net/355374/viewspace-510376/