存储过程动态在日期中

Question

我必须有一个动态的查询，但我的日期有问题。

错误消息是

msg 295，第16级，状态3，第45行
无法将字符串转换为数据类型SmallDateTime

这是我的过程

CREATE OR ALTER PROCEDURE [dbo].[baro_search1]
    (@name varchar(20),
     @begin_date varchar(30))
AS
BEGIN    
    SET NOCOUNT OFF;

    DECLARE @SQL VARCHAR(MAX)
    DECLARE @NomFilter  VARCHAR(MAX)
    DECLARE @DataFilter VARCHAR(MAX)
    DECLARE @all        VARCHAR(2)   = '1'

    SET @NomFilter = CASE WHEN @name IS NULL OR @name = '' THEN '''' + @all + ''' = ''' + @all + ''''
    ELSE 'Nome like ''%' + @name + '%'''
    END

    SET @DataFilter = CASE WHEN @begin_date IS NULL OR @begin_date = '' THEN '''' + @all + ''' = ''' + @all + ''''
    ELSE 'DataConsenso = ''%' + @begin_date + '%'''
    END

    SET @SQL = 'SELECT \*
    FROM \[dbo\].\[AnagDati\] anag
    WHERE '  + @NomFilter + ''
    \+ ' AND ' + @DataFilter + ''

    PRINT (@SQL)

    EXEC(@SQL)
END

EXEC \[dbo\].\[baro_search1\] 'name','2015-11-22 00:00:00'

Answer 1

我会这样做：

CREATE PROCEDURE dbo.baro_search1
  @name       varchar(20),
  @begin_date date
AS
BEGIN
  SET NOCOUNT ON;
  
  DECLARE @name_pattern varchar(22),
          @sql nvarchar(max) = N'SELECT * FROM dbo.AnagDati
            WHERE DataConsenso = @begin_date';

  IF LEN(COALESCE(RTRIM(@name), SPACE(0))) > 0
  BEGIN
    SET @name_pattern = CONCAT(char(37), @name, char(37));
    SET @sql += N' AND Nome LIKE @name_pattern;';
  END
  
  PRINT @sql;

  EXEC sys.sp_executesql @sql, 
    N'@begin_date date, @name_pattern varchar(22)',
    @begin_date, @name_pattern;
END

然后打电话给它：

EXEC dbo.baro_search1 @name = NULL,    @begin_date = '20220329';
EXEC dbo.baro_search1 @name = ' ',     @begin_date = '20220329';
EXEC dbo.baro_search1 @name = 'Aaron', @begin_date = '20220329';

• 尽可能多地避免在查询中加倍和四倍的单引号。而且，您应该始终努力将任何用户输入参数化以避免SQL注入，这意味着永远不要仅仅盲目地将可执行的字符串与用户输入串联。参见 Dynamic SQL 。
• 由于您无论如何都有动态的SQL，因此仅在您真正想检查它时才添加名称的条件，而不是总是进行检查，而是有一些令人费解的case表达式表达式简单地变成1 = 1。这样，当您需要搜索该列时，您将获得不同的计划。参见厨房sink> ：//sommarskog.se/dyn-search.html“ rel =“ nofollow noreferrer”>动态搜索条件。
• 切勿使用字符串数据类型通过日期，并始终使用明确的非区域日期格式。 yyyymmdd比yyyy-mm-dd ，尤其是在任何用户可以使用非US英语语言环境或语言时。参见负责任地约会。

Answer 2

将 @begin_date 的数据类型更改为 nvarchar 并且应该可以工作。