带有Select语句、执行动态SQL并返回值的表

Question

我有一个 select 语句，它返回一个充满 SELECT 语句的表（它遍历每个表中的每一列并创建一个 select 来查找该列是否包含任何错误数据）。

我需要获取这个充满 SELECT 语句的表，执行它们，并查看其中是否有任何返回行。如果计数(*)> 0，那么我想打印出一些数据。

我想我必须使用光标，但我不知道如何实现这一点。

这是我的代码来获取坏数据的计数。

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

这将返回一个表，其中包含如下行：

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

我需要执行此选择，并且如果 count(*) > > 0，然后打印第二列。我不想在结果或消息中显示任何内容，除非有数据要显示。

Answer 1

试试这个：

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

首先，我将您正在构建的 SQL 字符串稍微更改为：

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

当满足条件时，这将为您提供字符串，而当不满足条件时，将为您提供 NULL。

至于光标本身的机制：

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 可以接受输出参数：

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;