动态SQL调试

[heavyrain1234]

declare @sql nvarchar(1000)
DECLARE @ii INT=0
declare c1 cursor FOR
select 'select @ii=count(*) from '+name from sysobjects 
where xtype='U' and name like 'formmain_%' and (name not in ('formmain_0388','formmain_0795','formmain_0115'))   
GROUP BY name
HAVING count(*)<>0
open c1 
fetch next from c1 into @sql
while @@fetch_status=0
begin
  exec(@sql)
  PRINT @ii+@sql
  fetch next from c1 into @sql
end
close c1
deallocate c1

报如下错误：
消息 137，级别 15，状态 1，第 1 行
必须声明标量变量 "@ii"。
消息 245，级别 16，状态 1，第 13 行
在将 nvarchar 值 'select @ii=count(*) from formmain_0001' 转换成数据类型 int 时失败。

[二月十六]

试试这个

DECLARE @sql NVARCHAR(1000)
DECLARE @ii INT= 0
DECLARE c1 CURSOR
FOR
    SELECT  'select @ii=count(*) from ' + name
    FROM    sysobjects
    WHERE   xtype = 'U'
            AND name LIKE 'formmain_%'
            AND ( name NOT IN ( 'formmain_0388', 'formmain_0795',
                                'formmain_0115' ) )
    GROUP BY name
    HAVING  COUNT(*) <> 0
OPEN c1 
FETCH NEXT FROM c1 INTO @sql
WHILE @@fetch_status = 0
    BEGIN
        EXEC sp_executesql @sql, N'@ii int out', @ii OUT
        PRINT @ii + @sql
        FETCH NEXT FROM c1 INTO @sql
    END
CLOSE c1
DEALLOCATE c1

[二月十六]

DECLARE @sql NVARCHAR(1000)
DECLARE @ii INT= 0
DECLARE c1 CURSOR
FOR
    SELECT  'select @ii=count(*) from ' + name
    FROM    sysobjects
    WHERE   xtype = 'U'         
    GROUP BY name
    HAVING  COUNT(*) <> 0
OPEN c1 
FETCH NEXT FROM c1 INTO @sql
WHILE @@fetch_status = 0
    BEGIN
        EXEC sp_executesql @sql, N'@ii int out', @ii OUT
        PRINT RTRIM(@ii) + @sql
        FETCH NEXT FROM c1 INTO @sql
    END
CLOSE c1
DEALLOCATE c1

[二月十六]

好吧。。。最终版，用这个

DECLARE @sql NVARCHAR(1000)
DECLARE @ii INT= 0
DECLARE c1 CURSOR
FOR
    SELECT  'select @ii=count(*) from ' + name
    FROM    sysobjects
    WHERE   xtype = 'U'
            AND name LIKE 'formmain_%'
            AND ( name NOT IN ( 'formmain_0388', 'formmain_0795',
                                'formmain_0115' ) )
    GROUP BY name
    HAVING  COUNT(*) <> 0
OPEN c1 
FETCH NEXT FROM c1 INTO @sql
WHILE @@fetch_status = 0
    BEGIN
        EXEC sp_executesql @sql, N'@ii int out', @ii OUT
        PRINT RTRIM(@ii) + @sql
        FETCH NEXT FROM c1 INTO @sql
    END
CLOSE c1
DEALLOCATE c1

[道素]

你是想做什么呢，如果是想遍历所有相同前缀表，进行某种操作，也可以试试sp_msforeachtable 下面的语句就是遍历数据库中所有表名为formain，且不包含指定表的的表，统计每个表的行数 如果你想汇总数据，在语句在加一句，将每个表的结果都自动插入临时表

EXEC sp_msforeachtable 'select ''?'' as TableName, count(0) from ? '
,@whereand='and Schema_Id=Schema_id(''DBO'') and o.Name like ''formmain_%'' and (o.name not in (''formmain_0388'',''formmain_0795'',''formmain_0115''))'

[LongRui888]

从报错信息可以看出，是你的动态语句中引用到了 变量 @ii，但是你又没有在动态语句中定义这个变量，所以报错了 还有，你把动态生成的语句print出来了，你可以执行一下语句看看，到底报什么错误，看一下就知道哪儿有问题了