TSQL 动态 SQL 最佳方法

Question

我在下面的动态查询有效，但想知道我下面的内容是否可以优化，或者是否有更好的方法。

我有一个 Web 表单，用户在其中输入位置和收集日期。对于收集的日期，我有一个"收集的开始日期"和"收集的结束日期"。用户将"收集的结束日期"留空，在这种情况下，它将执行大于"收集的开始日期"的任何操作。

请注意我是如何做的 不是空 和下面的 1=1。 还想知道动态SQL是否是最佳方法，或者是否有更简单的方法可以做到这一点。

    DECLARE @sql varchar(max);
    SET @sql = 'SELECT * from tblProgram WHERE 1=1' 
    IF (@Location IS NOT  NULL)
    BEGIN
      SET @sql = @sql + ' AND Location = ' +  @Location
    END      
    IF (@FromDateCollected IS NOT  NULL AND @ToDateCollected IS NOT NULL)
    BEGIN
         SET @sql = @sql + ' AND pw.DateCollected >= ' +  QUOTENAME(convert(varchar, @FromDateCollected,101),'''') 
         + ' AND pw.DateCollected <= ' + QUOTENAME(convert(varchar, @ToDateCollected,101),'''')
    END
    ELSE IF (@FromDateCollected IS NOT  NULL AND @ToDateCollected IS  NULL)
    BEGIN
      SET @sql = @sql + ' AND pw.DateCollected >= ' +  QUOTENAME(convert(varchar, @FromDateCollected,101),'''') 
    END 
   exec(@sql)

Answer 1

好吧，

你可以按照 ta.speot.is 注释使用静态SQL做

WHERE x is null or x > date_column? 

但是，如果您坚持使用动态 SQL，则应使用参数化 SQL 语句sp_executeSQL

它更易于阅读，您不必使用引号名称，并且您可以免受SQL注入的影响

DECLARE @Location int 
DECLARE @FromDateCollected datetime 
DECLARE @ToDateCollected datetime
SET  @ToDateCollected  = '1/02/2012'

DECLARE @sql nvarchar(max)
DECLARE @ParmDefinition nvarchar(max)
SET  @ParmDefinition = N'@Location int , @FromDateCollected datetime, @ToDateCollected datetime ';
SET @sql = N'SELECT * from tblProgram WHERE 1=1' 
IF (@Location IS NOT  NULL)
BEGIN
   SET @sql = @sql + N' AND Location = @Location'
END      
IF (@FromDateCollected IS NOT  NULL AND @ToDateCollected IS NOT NULL)
BEGIN
   SET @sql = @sql + N' AND pw.DateCollected >= @FromDateCollected '
      + N' AND pw.DateCollected <= @ToDateCollected '
END
ELSE IF (@FromDateCollected IS NOT  NULL AND @ToDateCollected IS  NULL)
BEGIN
   SET @sql = @sql + N' AND pw.DateCollected >= @FromDateCollected' 
END 
exec sp_executesql @SQL, @ParmDefinition, @Location = @Location,
                                  @FromDateCollected = @FromDateCollected,
                                      @ToDateCollected = @ToDateCollected

演示