Selected Expression Table, Dynamic SQL Execution, and Return Values

Question

I have a select statement that returns a table full of SELECT statements (it goes through each column in each table and creates a selection to determine if this column contains any bad data).

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

I thought I should have used a cursor, but I have no idea how to do this.

Here is my code to get the amount 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 make this selection, and if count (*)> 0, then print the second column. I do not want to show anything in the results or messages, unless there is data to show.

Answer 1

try the following:

 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 2

First, I would modify the SQL string you are building a bit 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 will give you a string when the condition is met and NULL if 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 3

sp_executesql can take 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;