SQL查询正常，但动态查询失败

Question

我有一个查询，当它像这样运行时可以工作：

  declare @nvRecipients varchar(4000)
    ,@CustomerCode varchar(6)
    ,@start datetime
    ,@end datetime

 SELECT @CustomerCode = '10095'
       ,@start = '01/01/2011'
       ,@end = '02/01/2011'

  Select substring(PSD.DTSItemCode,1,4) As ItemCompanyCode , 
  convert(varchar(50),Cast(Sum(PSD.Quantity) as money),1) As TotalShipped 
  From  [AVANTISERVER\NCL_MASTER].AVANTI.dbo.PackingSlipHeader PSH 
  Inner Join  [AVANTISERVER\NCL_MASTER].AVANTI.dbo.PackingSlipsDetail PSD On PSH.PKSNumber = PSD.PKSNumber 
  Where Cast ( PKSDate As DateTime ) >= @start
    And Cast ( PKSDate As DateTime ) <= @end
    And PSD.Quantity > 0  
    And ( CompanyCode = @CustomerCode)
   Group By  substring(PSD.DTSItemCode,1,4) 
   Order By  substring(PSD.DTSItemCode,1,4) 

但是抛出错误

转换日期时间时出现语法错误 字符串。

当在提供参数 @customercode、@start 和 @end 的存储过程中像这样运行时：

       Set @nvQuery = ' Select substring(PSD.DTSItemCode,1,4) As ItemCompanyCode , '
  Set @nvQuery = @nvQuery + ' convert(varchar(50),Cast(Sum(PSD.Quantity) as money),1) As TotalShipped ' 
  Set @nvQuery = @nvQuery + ' From  [AVANTISERVER\NCL_MASTER].AVANTI.dbo.PackingSlipHeader PSH '
  Set @nvQuery = @nvQuery + ' Inner Join  [AVANTISERVER\NCL_MASTER].AVANTI.dbo.PackingSlipsDetail PSD On PSH.PKSNumber = PSD.PKSNumber '
  Set @nvQuery = @nvQuery + ' Where Cast ( PKSDate As DateTime ) >= ''' + @start + ''' '
  Set @nvQuery = @nvQuery + '   And Cast ( PKSDate As DateTime ) <= ''' + @end + ''' '
  Set @nvQuery = @nvQuery + '   And PSD.Quantity > 0 '  
  Set @nvQuery = @nvQuery + '   And ( CompanyCode = ''' + @CustomerCode + ''') '
  Set @nvQuery = @nvQuery + '  Group By  substring(PSD.DTSItemCode,1,4) '
  Set @nvQuery = @nvQuery + '  Order By  substring(PSD.DTSItemCode,1,4) '

任何人都可以看到我的错误吗？我好像找不到啊数据全部用 ISDATE() 检验。

Answer 1

如果它们是日期，则需要将它们转换为 varchar 以附加到查询中。

您无法将日期时间连接到字符串而不先进行转换：

Set @nvQuery = @nvQuery + '   And Cast( PKSDate As DateTime) <= ''' + CAST(@end AS VARCHAR(20)) + ''' '

---

  Set @nvQuery = ' Select substring(PSD.DTSItemCode,1,4) As ItemCompanyCode , '
  Set @nvQuery = @nvQuery + ' convert(varchar(50),Cast(Sum(PSD.Quantity) as money),1) As TotalShipped ' 
  Set @nvQuery = @nvQuery + ' From  [AVANTISERVER\NCL_MASTER].AVANTI.dbo.PackingSlipHeader PSH '
  Set @nvQuery = @nvQuery + ' Inner Join  [AVANTISERVER\NCL_MASTER].AVANTI.dbo.PackingSlipsDetail PSD On PSH.PKSNumber = PSD.PKSNumber '
  Set @nvQuery = @nvQuery + ' Where Cast ( PKSDate As DateTime ) >= ''' + CONVERT(varchar(20), @start, 101) + ''' '
  Set @nvQuery = @nvQuery + '   And Cast ( PKSDate As DateTime ) <= ''' + CONVERT(varchar(20), @end, 101) + ''' '
  Set @nvQuery = @nvQuery + '   And PSD.Quantity > 0 '  
  Set @nvQuery = @nvQuery + '   And ( CompanyCode = ''' + @CustomerCode + ''') '
  Set @nvQuery = @nvQuery + '  Group By  substring(PSD.DTSItemCode,1,4) '
  Set @nvQuery = @nvQuery + '  Order By  substring(PSD.DTSItemCode,1,4) '

Answer 2

为了省去很多麻烦，您也可以在动态 SQL 中使用参数，是的！ - 参数，就像您的实际查询一样。

  Set @nvQuery = ' Select substring(PSD.DTSItemCode,1,4) As ItemCompanyCode , '
  Set @nvQuery = @nvQuery + ' convert(varchar(50),Cast(Sum(PSD.Quantity) as money),1) As TotalShipped ' 
  Set @nvQuery = @nvQuery + ' From  [AVANTISERVER\NCL_MASTER].AVANTI.dbo.PackingSlipHeader PSH '
  Set @nvQuery = @nvQuery + ' Inner Join  [AVANTISERVER\NCL_MASTER].AVANTI.dbo.PackingSlipsDetail PSD On PSH.PKSNumber = PSD.PKSNumber '
  Set @nvQuery = @nvQuery + ' Where Cast ( PKSDate As DateTime ) >= @start '
  Set @nvQuery = @nvQuery + '   And Cast ( PKSDate As DateTime ) <= @end '
  Set @nvQuery = @nvQuery + '   And PSD.Quantity > 0 '  
  Set @nvQuery = @nvQuery + '   And ( CompanyCode = @CustomerCode) '
  Set @nvQuery = @nvQuery + '  Group By  substring(PSD.DTSItemCode,1,4) '
  Set @nvQuery = @nvQuery + '  Order By  substring(PSD.DTSItemCode,1,4) '

而不是通过

EXEC (@nvQuery)

使用表单

exec sp_executeSQL @nvQuery,
     N'@start datetime,@end datetime,@customerCode varchar(6)', --list of params
     @start, @end, @customerCode -- params, matching list

来运行它，这可以解决各种字符串操作、转换/格式问题。例如

declare @start datetime,@end datetime,@customerCode varchar(6)
select @start = getdate(), @end = getdate()+2, @customerCode = 'TEST'

set @nvQuery ... -- build the statement

exec sp_executeSQL @nvQuery,
     N'@start datetime,@end datetime,@customerCode varchar(6)', --list of params
     @start, @end, @customerCode -- params, matching list