Table with Select Statements, Executing Dynamic SQL and Returning Values

Question

I have a select statement that returns a table full of SELECT statements (It goes through every column in every table and creates a select to find if that column contains any bad data).

I need to take this table full of SELECT statements, execute them, and see if any of them return rows. If the count(*) > 0, then I want to print out some data.

I was thinking I had to use a cursor, but I have no idea how I would accomplish that.

Here is my code to get the count of bad data.

SELECT 'SELECT count(*),  '' '+sysobjects.name + ' - ' + syscolumns.name + 
    ' '' FROM ['
         +sysobjects.name + '] WHERE UNICODE(SUBSTRING(['+syscolumns.name+'],Len(['+syscolumns.name+']),1)) = 0' 
         FROM sysobjects 
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype=systypes.xtype
   WHERE sysobjects.xtype='U' and systypes.name IN ('varchar', 'nvarchar')
ORDER BY sysobjects.name,syscolumns.colid

This returns a table with rows like:

SELECT count(*),  ' All_MW_Users - LastName ' FROM [All_MW_Users] WHERE UNICODE(SUBSTRING([LastName],Len([LastName]),1)) = 0

I need to execute this select, and if the count(*) > 0, then print the second column. I don't want to show anything in the results or messages unless there is data to show.

Answer 1

First, I'd change the SQL string you're building slightly to be

SELECT CASE WHEN count(*)>0 THEN ' All_MW_Users - LastName ' END FROM [All_MW_Users] WHERE UNICODE(SUBSTRING([LastName],Len([LastName]),1)) = 0 

This would get you the string when the condition is met and NULL when it is not.

As for the mechanics of the cursor itself:

declare @SQLSTring nvarchar(4000)

create table #tmpResults (
    OutputString nvarchar(1000)
)

declare DynamicSQL cursor for
    {The Select Statement in your question with modification}

open DynamicSQL

while (1=1) begin
     fetch next from DynamicSQL
        into @SQLString

    if @@fetch_status <> 0
        break;

   insert into #tmpResults
       (OutputString)
       exec sp_executesql @SQLString
end /* while */

close DynamicSQL
deallocate DynamicSQL

select OutputString
    from #tmpResults
    where OutputString is not null

Answer 2

try this:

DECLARE @SQL nvarchar(max)
SET @SQL='DECLARE @TempTable table (RowID int identity(1,1), CountOf int, DescriptionOf nvarchar(500));'
SELECT @SQL=@SQL+';INSERT @TempTable (CountOf,DescriptionOf ) SELECT count(*),  '' '+sysobjects.name + ' - ' + syscolumns.name + 
    ' '' FROM ['
         +sysobjects.name + '] WHERE UNICODE(SUBSTRING(['+syscolumns.name+'],Len(['+syscolumns.name+']),1)) = 0' 
         FROM sysobjects 
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype=systypes.xtype
   WHERE sysobjects.xtype='U' and systypes.name IN ('varchar', 'nvarchar')
ORDER BY sysobjects.name,syscolumns.colid

SET @SQL=@SQL+';SELECT * FROM @TempTable WHERE CountOF>0' --make sure there is no truncation of the commands

EXEC (@SQL)

Answer 3

sp_executesql can accept output parameters:

declare c cursor static forward_only read_only for
SELECT N'SELECT @count = count(*)' +
    N' FROM ' + quotename(s.name) + '.' + quotename(t.name) +
    N' WHERE UNICODE(SUBSTRING(' + quotename(c.name) + N', len('+ quotename(c.name) + N'),1)) = 0x00'
    , s.name as schema_name
    , t.name as table_name
    , c.name as column_name
    from sys.tables t
    join sys.schemas s on t.schema_id = s.schema_id
    join sys.columns c on t.object_id = c.object_id
    join sys.types x on c.user_type_id = x.user_type_id
    where x.name in (N'varchar', N'nvarchar');

open c;

declare @sql nvarchar(max), @s sysname, @t sysname, @c sysname;
fetch next from c into @sql, @s, @t, @c;
while 0 = @@fetch_status
begin
    declare @count bigint = 0;
    print @sql;
    exec sp_executesql @sql, N'@count bigint output', @count output;
    raiserror (N'%s.%s.%s: %I64d', 0,1, @s, @t, @c, @count);
                -- if @count is not 0, act here
    fetch next from c into @sql, @s, @t, @c;
end

close c;
deallocate c;