DynamicSqlPart2.sql

-- Declare a Cursor to read #Results and generate rows from it
DECLARE C CURSOR FOR
-- Select statement to be driven by the cursor
SELECT [SchemaName], [TableName], [ColumnName]
FROM #Results
ORDER BY    [SchemaName], [TableName], [ColumnName]

-- Open the Cursor (row pointer)
    OPEN C

-- Fetch a row from the cursor query and push to variables
    FETCH NEXT FROM C
     INTO @schema_name, @table_name, @column_name
-- While we are able to fetch rows
WHILE @@FETCH_STATUS = 0
      BEGIN
-- Create a dynamic query out of this data
	SET @SQL = 'SELECT @row_count=COUNT(*), ' +
	'@value_found='+ @column_name + ',' +
	'@company_id='+ '[CompanyID]' + 
-- Future enhancement to add company_id to result
	--' (CASE WHEN EXISTS (SELECT cols.object_id AS DoesExist FROM sys.columns cols JOIN sys.tables  tabl ON tabl.object_id = cols.object_id  WHERE cols.name=''' + 'CompanyID' + ''' AND tabl.name=''' + @table_name + ''')' +
	--', @company_id=(CASE WHEN EXISTS (SELECT *
    --                   FROM INFORMATION_SCHEMA.COLUMNS 
    --                   WHERE TABLE_NAME=' + '''' + @table_name + '''' + '
	--				   AND COLUMN_NAME=' + '''' + 'CompanyID' + '''' + ')' + -- and schema name too, if you like
    --' THEN [CompanyID]' +
    --' ELSE 0 END)' +
		' FROM ['+ @schema_name +'].['+ @table_name +']'+
	    ' WHERE ' + @column_name + @SearchOperator + '''' + @SearchValue + '''' +
		' GROUP BY ' + @column_name + ',CompanyID'
-- Execute the dynamic query and push its output to our variables.
--PRINT @SQL
		EXECUTE sp_executeSQL @SQL, N'@row_count INT OUTPUT, @value_found varchar(128) OUTPUT, @company_id INT OUTPUT', @row_count=@row_count OUTPUT, @value_found=@value_found OUTPUT, @company_id=@company_id OUTPUT
-- Check to see if the query had results
		IF (@row_count > 0) 
-- If so, create an update (or delete) query to handle the rows found
		BEGIN
-- Build the update query
			SET @USQL = 'UPDATE ' + '['+ @schema_name +'].['+ @table_name +']' +
					' SET ' + @column_name + '=''' + @NewValue + '''' +
					' WHERE ' + @column_name + '=''' + @value_found + ''''
-- Build the delete query
			SET @DSQL = 'DELETE FROM ' + '['+ @schema_name +'].['+ @table_name +']' +
					' WHERE ' + @column_name + '=''' + @value_found + ''''

-- Insert the result into the virtual @meta table
	      INSERT INTO @meta ([schema_name], [table_name], [column_name], [search_value], [value_found], [row_count], [update_script], [delete_script], [company_id])
			VALUES (@schema_name, @table_name, @column_name, @SearchValue, @value_found, @row_count, @USQL, @DSQL, @company_id)
		END
-- Keep fetching and go back to while
      FETCH NEXT FROM C
      INTO @schema_name, @table_name, @column_name
END
-- Finished fetching, close and deallocate cursor
CLOSE C
DEALLOCATE C