Stored procedure dynamic where with date

Question

I must have a query with a dynamic where but I have a problem with the dates.

The error message is

Msg 295, Level 16, State 3, Line 45
Failed to convert a string to data type smalldatetime

Here is my procedure

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

I would do it this way:

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

Then to call it:

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';

• Do as much as you can to avoid doubling and quadrupling single quotes in your queries. And you should always strive to parameterize any user input to avoid SQL injection, which means never just blindly concatenating executable strings with user input. See Dynamic SQL.
• Since you have dynamic SQL anyway, only add the condition for name when you actually want to check for it, rather than always having the check but having some convoluted CASE expression that simply becomes 1=1. This way you get a different plan when you need to search that column, compared to when you don't. See Kitchen Sink and Dynamic Search Conditions.
• Never pass dates using a string data type, and always use unambiguous, non-regional date formats. yyyyMMdd is infinitely better than yyyy-MM-dd, especially if any users may use a non-US English locale or language. See Dating Responsibly.