SQL 2012 – SearchAllTables for datetime Stored procedure

I’m trying to create a stored procedure that will search all user created tables of a database for a search string.

I found this stored procedure that I’d like to start off with and build on.

  • Could not load file or assembly Microsoft.SqlServer.SqlClrProvider on production ASP.net site, where is it?
  • Rewriting correlated subquery as JOIN?
  • Sum of child levels total in a Hierarchy
  • Can I set a query timeout when using Zend_Db_Adapter_Pdo_Mssql?
  • Is there a SQL Server 2008 method to group rows in a table so as to behave as a nested table?
  • SQL Server 2005 Setting a variable to the result of a select query
  • http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

    The only problem is entering a datetime into the search will always return nothing.

    How can I modify this to search for dates as well?

    Lines of interest:

    CREATE PROC SearchAllTables
    @SearchStr nvarchar(100)
        SET @SearchStr2 = CASE WHEN ISDATE(@SearchStr)=0
                      THEN QUOTENAME('%' + @SearchStr + '%','''')
                      ELSE @SearchStr END
        --Here's where the comparison is made. This comparison works for string and numeric types but not      datetime
         DECLARE @sql nvarchar(max)
                IF ISDATE(@SearchStr) = 0       
                    SET @sql = 'INSERT INTO #Results SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                        FROM ' + @TableName + ' (NOLOCK) ' +
                        ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                ELSE IF ISDATE(@SearchStr) = 1  
                    SET @sql = 'INSERT INTO #Results SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                        FROM ' + @TableName + ' (NOLOCK) ' +
                        ' WHERE DATEDIFF(day, CONVERT(datetime, '+ @ColumnName + ', 103), ' + @SearchStr+ ') = 0'   
                PRINT @sql
                EXEC sp_ExecuteSQL @sql 

    I get a conversion error. I need this stored procedure to work with all 3 types of data string, numeric, and date.

    Conversion failed when converting date and/or time from character string.

    Thank you

    3 Solutions collect form web for “SQL 2012 – SearchAllTables for datetime Stored procedure”

    your date field is a datetime?

    you should traet it like this:

    declare @SearchStr datetime
    set @SearchStr = convert(datetime, '2012-09-10', 103) –- dd/mm/yyyy

    and the where part like this:

    'WHERE  Datediff(day, CONVERT(datetime, '+ @ColumnName + ', 103), ' + @SearchStr+') = 0'

    I think this should work

    If the search string is a date then you have to do an explicit conversion. Otherwise string will not match with datetime.

    Try something like this.

    SET @SearchStr2 = CASE WHEN ISDATE(@SearchStr)=1 
      THEN CONVERT(datetime,@SearchStr,103)
      ELSE QUOTENAME('%' + @SearchStr + '%','''') END

    Hope this will help you.

    ElVieejo’s answer was correct but for it to finally work I also needed to SET @ColumnName conditionally (depending IF ISDATE(@SearchStr) ). If @SearchStr is a date, the inner query of SET @ColumnName had to EXCLUDE all types except the ‘datetime’ under “DATA_TYPE IN (‘datetime’)”.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.