我创建了一个存储过程,用于使用包括日期范围在内的多个字段获取搜索数据。我的过程通过所有搜索参数成功返回数据,但不是从日期范围返回数据。
这是我的存储过程:
ALTER PROCEDURE getAssessmentDetails
@admin VARCHAR(100),
@SearchText VARCHAR(500) = '',
@Status VARCHAR(500) = '',
@Supervisor VARCHAR(500) = '',
@Process VARCHAR(500) = '',
@Skill VARCHAR(500) = '',
@Designation VARCHAR(500) = '',
@StartDate DATETIME = '',
@EndDate DATETIME = ''
AS
BEGIN
DECLARE @Query VARCHAR(MAX)=''
DECLARE @Params VARCHAR(MAX)=''
SELECT @StartDate = CONVERT(varchar, @StartDate, 120), @EndDate = CONVERT(varchar, @EndDate, 120)
SET @Query = 'select * from ases_admin WITH(NOLOCK) where admin = ' + @admin + ' and timestamp between '+@StartDate+ ' and ' +@EndDate
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @SearchText<>''
BEGIN
SET @Params+=' AND (asesname LIKE ''%'+@SearchText+'%'' ) '
END
IF @Status<>''
BEGIN
SET @Params+=' AND asesstatus IN('+@Status+') '
END
IF @Supervisor<>''
BEGIN
SET @Params+=' AND supecode IN('+@Supervisor+') '
END
IF @Process<>''
BEGIN
SET @Params+=' AND procid IN('+@Process+') '
END
IF @Skill<>''
BEGIN
SET @Params+=' AND skid IN('+@Skill+') '
END
IF @Designation<>''
BEGIN
SET @Params+=' AND desigid IN('+@Designation+') '
END
EXEC (@Query+@Params)
END
GO
当我输入日期为 @StartDate = '2018-02-08'和 @EndDate = '2018-05-07' 时,出现以下错误 -
从字符串转换日期和/或时间时转换失败
仅供参考 - 我正在使用node.js与SQL Server和Angular.js
您需要将日期字符串括在单引号中
+ ' and timestamp between ''' +@StartDate+ ''' and ''' +@EndDate+ ''''
与其编写动态查询并检查每个参数的空白,只需编写普通查询并执行即可。 例如,只需写,
select * from ases_admin WITH(NOLOCK) where admin = @admin
and timestamp between @StartDate and @EndDate
and (asesname LIKE' %'+@SearchText+'%' or @SearchText='')
括号中的最后一个条件在搜索文本为空白"或有一些文本时都工作正常。 您可以附加其他类似条件
您正在尝试将DATETIME
变量连接到字符串。
试试这个:
SET @Query = 'select * from ases_admin WITH(NOLOCK) where admin = ' +
@admin + ' and timestamp between ' +
CONVERT(VARCHAR,@StartDate, 121) + ' and ' +
CONVERT(VARCHAR,@EndDate , 121)