如何在SQL动态查询中传递日期时间？

debugcn 发表于 Dev

耕作

我已经写了这样的存储过程

ALTER PROCEDURE [dbo].[spLoadPendingPaymentSheetByFilter] --'2015-04-01','2015-04-02','Select-One','Select-One','Select-One',''   
    @FromDate as datetime,
    @ToDate as datetime,
    @Status as nvarchar(50),
    @Remarks as nvarchar(50),
    @Paymenttype as nvarchar(50),
    @BillID as nvarchar(50)      
AS 
    Declare @Where as nvarchar(max)

    set @Where = '( MenifestDate BETWEEN  ''' +   CONVERT(VARCHAR(10),@FromDate, 101) + ''' and ''' + CONVERT(VARCHAR(10),@ToDate, 101) + ''' )'

    if(@Status <> 'Select-One')
       set @Where = 'Status = '+ @Status 

    if(@Remarks <> 'Select-One')
       set @Where = @Where + 'and Remarks = '+ @Remarks 

    if(@Paymenttype <> 'Select-One')
       set @Where = @Where + 'and PaymentType = ' + @Paymenttype 

    if(@BillID <> '')
       set @Where = @Where + 'and BillID = '+ @BillID 

    Declare @SelectString as nvarchar(1000)  
    set @SelectString = 'SELECT MasterID,BillID, MenifestDate, FarwardingNo,ReceverCountryName,Status,Remarks,PaymentType    
     FROM tblMenifest   
     WHERE ' + @Where

     exec @SelectString

当我执行它时，我得到了这个错误

名称'SELECT MasterID，BillID，MenifestDate，FarwardingNo，ReceverCountryName，Status，Remarks，PaymentType FROM tblMenifest WHERE（MenifestDate BETWEEN '04 / 01/2015'和'04 / 02/2015'）'不是有效的标识符

该MenifestDate列数据类型是datetime。

吉姆

相信您要放EXEC(@SelectString)而不是放exec @SelectString。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。