sql - 将日期时间存储过程参数转换为动态 T-SQL 查询中的过滤器(在 WHERE 子句中)

Question

下面是我试图动态化的存储过程中的一段代码。这意味着我想运行 EXEC('select * from table_name WHERE filter=something') 以将结果集返回到我的 Web 服务。我正在查询的表中的 week_start_date 字段是日期时间字段，而不是日期字段。如何正确地将 datetime 参数附加到动态 SQL，以便它在查询中正确解释它？此外，如果您有关于如何使代码更清晰的建议，我会采纳建议。

当我查看数据时，我的值如下所示:

2012-08-10 00:00:00.000

无效的 T-SQL 代码:

ALTER PROCEDURE [dbo].[logged_time_by_week]
    @week_start_date_filter datetime = null
AS
BEGIN

....

    DECLARE @select_clause nvarchar(4000);
    DECLARE @from_clause nvarchar(4000);
    DECLARE @where_clause nvarchar(4000);
    DECLARE @order_clause nvarchar(4000);

    SET @select_clause = 
        ' SELECT '
        + ' [sunday_hours],'
        + ' [monday_hours],'
        + ' [tuesday_hours],'
        + ' [wednesday_hours],'
        + ' [thursday_hours],'
        + ' [friday_hours],'
        + ' [saturday_hours]'

    SET @from_clause =
        ' FROM '
        + ' [timesheet_row] '

    SET @where_clause = ''

    IF @week_start_date_filter IS NOT NULL AND @week_start_date_filter != ''
    BEGIN 
        IF @where_clause != ''
        BEGIN
            SET @where_clause = @where_clause + ' AND ' 
        END
        SET @where_clause = @where_clause + ' [week_start_date] = ' + CONVERT(nvarchar, @week_start_date_filter)
    END

    IF @where_clause != '' 
    BEGIN
        SET @where_clause = ' WHERE ' + @where_clause
    END

    SET @order_clause = ' ORDER BY [' + @sort_column + '] ' + @sort_direction

    EXEC(@select_clause + @from_clause + @where_clause + @order_clause)

Answer 1

解决此类错误的最简单方法是打印出生成的 sql 以查看其失败的原因。

只要查看您的查询，我相信您只需要修复 @week_start_date_filter 变量周围的引号。

declare @week_start_date_filter datetime = getdate();
print ' [week_start_date] = ''' + CONVERT(nvarchar, @week_start_date_filter) + '''';

另请注意，如果您必须使用动态 sql，我建议改用参数化 sql。这是一个使用 sys.objects 的示例，因为我不知道您的表的架构。

DECLARE
    @week_start_date_filter datetime    = (SELECT TOP(1) [create_date] FROM sys.objects),
    @sort_column            sysname     = N'create_date',
    @sort_direction         nvarchar(3) = N'ASC';


    DECLARE @select_clause nvarchar(4000);
    DECLARE @from_clause nvarchar(4000);
    DECLARE @where_clause nvarchar(4000);
    DECLARE @order_clause nvarchar(4000);

    SET @select_clause = 
        ' SELECT TOP(1) '
        + ' *';

    SET @from_clause =
        ' FROM '
        + ' sys.objects'

    SET @where_clause = ''

    IF @week_start_date_filter IS NOT NULL AND @week_start_date_filter != ''
    BEGIN 
        IF @where_clause != ''
        BEGIN
            SET @where_clause = @where_clause + ' AND ' 
        END
        SET @where_clause = @where_clause + ' [create_date] = @date_filter'
    END

    IF @where_clause != '' 
    BEGIN
        SET @where_clause = ' WHERE ' + @where_clause
    END

    SET @order_clause = ' ORDER BY [' + @sort_column + '] ' + @sort_direction

    DECLARE
        @SQL    nvarchar(max) = @select_clause + @from_clause + @where_clause + @order_clause,
        @Params nvarchar(200) = N'@date_filter datetime';


    EXEC sp_executesql
        @stmt        = @SQL,
        @params      = @Params,
        @date_filter = @week_start_date_filter;