begin tran
declare @i int
declare @sql varchar(max)
declare @strsql varchar (max)
set @i =1
while @i <4
begin
select @sql=isnull(@sql+'+','')+'(case when exists(select 1 from dbo.RPT_resultA41 where '+name+' is null) then 0 else 1 end)'
from sys.syscolumns
where id =OBJECT_ID('RPT_resultA41') and name like 'Mtd201%'
set @strsql='update dbo.RPT_resultA41 set Ytd =(select SUM(ISNULL(Mtd201204,0)+ISNULL(Mtd201205,0)+ISNULL(Mtd201206,0)
+ISNULL(Mtd201207,0)+ISNULL(Mtd201208,0)+ISNULL(Mtd201209,0)
+ISNULL(Mtd201210,0)+ISNULL(Mtd201211,0)+ISNULL(Mtd201212,0))
from dbo.RPT_resultA41
where classify=-1 and CompanyID='+@i+')
where classify=-1 and CompanyID='+@i
print (@srtsql)
set @i=@i+1 set @sql=''
set @strsql='' end
报错心须声明变量,昨天解决了个这样的问题,今天怎样都想不起来了,无脑了
declare @i int
declare @sql varchar(max)
declare @strsql varchar (max)
set @i =1
while @i <4
begin
select @sql=isnull(@sql+'+','')+'(case when exists(select 1 from dbo.RPT_resultA41 where '+name+' is null) then 0 else 1 end)'
from sys.syscolumns
where id =OBJECT_ID('RPT_resultA41') and name like 'Mtd201%'
set @strsql='update dbo.RPT_resultA41 set Ytd =(select SUM(ISNULL(Mtd201204,0)+ISNULL(Mtd201205,0)+ISNULL(Mtd201206,0)
+ISNULL(Mtd201207,0)+ISNULL(Mtd201208,0)+ISNULL(Mtd201209,0)
+ISNULL(Mtd201210,0)+ISNULL(Mtd201211,0)+ISNULL(Mtd201212,0))
from dbo.RPT_resultA41
where classify=-1 and CompanyID='+@i+')
where classify=-1 and CompanyID='+@i
print (@srtsql)
set @i=@i+1 set @sql=''
set @strsql='' end
报错心须声明变量,昨天解决了个这样的问题,今天怎样都想不起来了,无脑了
解决方案 »
- sql2005 一个表中有100万条记录,算多不?
- 在一个表中,有三个字段,求完全相同的记录只显示一条,有一个不相同,则显示出来!
- 高分求救!求一条SQL查询语句!查一个表最新5分钟的数据怎么写?
- ms sql server无故自动关闭服务,紧急求助
- 级联更新和级联删除的问题
- SQL Server中导入数据的问题
- 我要通过函数计算一种统计结果放在输出字段中,但该函数中需要执行动态SQL,而函数中不允许执行动态SQL,提示错误:在函数内的'insert exec' 对带副作用的运算符使用无效
- 怎样才能实现,GROUP BY OR ?
- 更新数据问题。。请大虾帮忙看看
- SqlServer中的数据库中的dtpro...怎么是用户表
- sql语句变量的声明了引用
- SQL SEVRER如何给空值填上跟上一行一样的值
DECLARE @i INT
DECLARE @sql VARCHAR(MAX)
SET @i = 1
WHILE @i < 4
BEGIN
--declare @strsql varchar (max)
DECLARE @strsql VARCHAR(MAX) =''
SELECT @sql = ISNULL(@sql + '+', '')
+ '(case when exists(select 1 from dbo.RPT_resultA41 where '
+ name + ' is null) then 0 else 1 end)'
FROM sys.syscolumns
WHERE id = OBJECT_ID('RPT_resultA41')
AND name LIKE 'Mtd201%'
SET @strsql = 'update dbo.RPT_resultA41 set Ytd =(select SUM(ISNULL(Mtd201204,0)+ISNULL(Mtd201205,0)+ISNULL(Mtd201206,0)
+ISNULL(Mtd201207,0)+ISNULL(Mtd201208,0)+ISNULL(Mtd201209,0)
+ISNULL(Mtd201210,0)+ISNULL(Mtd201211,0)+ISNULL(Mtd201212,0))
from dbo.RPT_resultA41
where classify=-1 and CompanyID=' + CONVERT(VARCHAR(10),@i )
+ ')
where classify=-1 and CompanyID=' + CONVERT(VARCHAR(10),@i )
PRINT (@strsql)
SET @i = @i + 1
SET @sql = ''
SET @strsql = ''
END
declare @i int
set @i =1
while @i <4
begin
declare @strsql varchar (max)
set @strsql=''
declare @sql varchar(max)
set @sql=''
select @sql=isnull(@sql+'+','')+'(case when exists(select 1 from dbo.RPT_resultA41 where '+name+' is null) then 0 else 1 end)'
from sys.syscolumns
where id =OBJECT_ID('RPT_resultA41') and name like 'Mtd201%'
set @strsql='update dbo.RPT_resultA41 set Ytd =(select SUM(ISNULL(Mtd201204,0)+ISNULL(Mtd201205,0)+ISNULL(Mtd201206,0)
+ISNULL(Mtd201207,0)+ISNULL(Mtd201208,0)+ISNULL(Mtd201209,0)
+ISNULL(Mtd201210,0)+ISNULL(Mtd201211,0)+ISNULL(Mtd201212,0))
from dbo.RPT_resultA41
where classify=-1 and CompanyID='+ltrim(@i)+')
where classify=-1 and CompanyID='+ltrim(@i)
print (@strsql)
set @i=@i+1 set @sql=''
set @strsql='' end
应该是print @strsql