Home Forums Article Discussions Article Discussions by Author Discuss content posted by Timothy A Wiseman Finding Values With Numerous Columns RE: Finding Values With Numerous Columns

  • December 3, 2008 at 10:01 am

    #907010

    Douglas Osborne (12/3/2008)

    Hey,

    This didn't work for me until I added a % at the end of the first dynamic SQL statement:

    SELECT @sql = CASE

    WHEN @sql IS NULL THEN 'Select ''' + ColumnName + ''' as ContainingColumn, * From ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName) + ' where ' + ColumnName + ' like ''' + @Value + -- Add below

    ' %'' '

    WHEN @sql IS NOT NULL THEN @sql + 'UNION ALL Select ''' + ColumnName + ''' as ContainingColumn, * From ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName) + ' where ' + ColumnName + ' like ''' + @Value + ''' '

    Doug

    Adding the % directly in the dynamic sql statement forces it to always look for anything that starts with the value passed in. The way I use it is to put the % in the value passed in, just like using like directly in a select statement. That way I can have it search for the beginning or ending or any combination.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/