关于动态SQL的问题,请高手帮忙,分不够再加(在线等待)

zearbo 2003-05-19 08:35:28
CREATE PROCEDURE GetRemoteDeptScore
@subcenter_no varchar(6),
@start_time varchar(10),
@end_time varchar(10),
@area_type int
AS

create table #temp(
article_type_no int,
integration_no int,
score float
)

declare @center_server nvarchar(20)
declare @article_type_no int
declare @integration_no int
declare @old_article_type_no int
declare @score float
declare @flag bit
declare @sqlstr nvarchar(255)


select @center_server = rtrim(server_name) from subcenter where subcenter_area_no = '1'

---初始化数据首先将文章类型及综合标志对应的业绩置0
DECLARE init_temp CURSOR LOCAL FOR
SELECT a.article_type_no, i.integration_no from integration_sign i,article_type a
WHERE a.article_type_no = i.article_type_no and a.area_type =@area_type

OPEN init_temp
IF @@error != 0 goto errorpos
IF @@CURSOR_ROWS = 0 GOTO ERRORPOS
set @flag = 0
FETCH NEXT FROM init_temp INTO

@article_type_no ,
@integration_no

------------------读入第一条记录--------------综合标志业绩初始化------------------------------------------------------------
if @@fetch_status = 0
begin
insert #temp (article_type_no,integration_no,score) values(@article_type_no, @integration_no, 0)
set @flag = 1
end
IF @@error != 0 goto errorpos
set @old_article_type_no = @article_type_no

WHILE @@FETCH_STATUS =0
BEGIN

FETCH NEXT FROM init_temp INTO
@article_type_no ,
@integration_no

IF(@old_article_type_no = @article_type_no)
begin
if @@fetch_status = 0
insert #temp (article_type_no,integration_no,score) values(@article_type_no, @integration_no, 0)
IF @@error != 0 goto errorpos
end
ELSE
begin
----------------对应文章的编译人合计业绩-----------------------------------
insert #temp (article_type_no,integration_no,score) values(@old_article_type_no, -2000, 0)
insert #temp (article_type_no,integration_no,score) values(@article_type_no, @integration_no, 0)
IF @@error != 0 goto errorpos
SET @old_article_type_no = @article_type_no
end
END
if @flag = 1 insert #temp (article_type_no,integration_no,score) values(@old_article_type_no, -2000, 0)
CLOSE init_temp
DEALLOCATE init_temp
---------------------------初始化结束------------------------------------------------------------
--------计算某分中心对应的合计编辑业绩

set @sqlstr =N' select distinct article_type_no, sum(score) score from '+@center_server+'link.mtsingle.dbo.section_achievement
where subcenter_no = '''+@subcenter_no +''' and (stat_date between '''+@start_time +'''and''' +@end_time+''')
group by article_type_no '
--出错位置
DECLARE redac_total CURSOR LOCAL FOR
select @sqlstr
OPEN redac_total
IF @@ERROR != 0 goto errorpos
IF @@CURSOR_ROWS = 0 GOTO ERRORPOS
FETCH NEXT FROM redac_total INTO
@article_type_no,
@score
if @@fetch_status = 0
update #temp set score = @score where article_type_no = @article_type_no and integration_no = -2000
IF @@ERROR != 0 goto errorpos
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM redac INTO
@article_type_no,
@score
if @@fetch_status = 0
update #temp set score = @score where article_type_no = @article_type_no and integration_no = -2000
IF @@ERROR != 0 goto errorpos
END

CLOSE redac_total
DEALLOCATE redac_total

-------------合计编辑业绩计算结束
-------------计算分类编辑业绩(按文章类别和综合标志)

set @sqlstr = N' select distinct article_type_no,integration_sign_no ,sum(score) score from '+@center_server+'link.mtsingle.dbo.section_achievement
where subcenter_no = '''+@subcenter_no+''' and (stat_date between '''+@start_time +'''and '''+@end_time+''')
group by article_type_no , integration_sign_no
order by article_type_no, integration_sign_no'
--------------------------print @sqlstr
DECLARE redac_integration CURSOR LOCAL FOR
select (@sqlstr)

OPEN redac_integration
IF @@ERROR != 0 goto errorpos
IF @@CURSOR_ROWS = 0 GOTO ERRORPOS
FETCH NEXT FROM redac_integration INTO
@article_type_no,
@integration_no,
@score
if @@fetch_status = 0
UPDATE #temp SET score = @score WHERE article_type_no = @article_type_no and integration_no = @integration_no
IF @@ERROR != 0 goto errorpos
WHILE @@FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM redac_integration INTO
@article_type_no,
@integration_no,
@score
if @@fetch_status = 0
UPDATE #temp SET score = @score WHERE article_type_no = @article_type_no and integration_no = @integration_no
IF @@ERROR != 0 goto errorpos

END

SELECT * FROM #temp
RETURN
-----出错返回出口
errorpos:
SELECT * FROM #temp
return
GO



服务器: 消息 16924,级别 16,状态 1,过程 GetRemoteDeptScore,行 85
[Microsoft][ODBC SQL Server Driver][SQL Server]Cursorfetch: 在 INTO 列表中声明的变量数目必须与所选择的列数目匹配。
@RETURN_VALUE = N/A
...全文
9 点赞 收藏 11
写回复
11 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
zearbo 2003-05-19
谢谢各位,采用临时表好使结贴
回复
zearbo 2003-05-19

CREATE PROCEDURE GetRemoteDeptScore
@subcenter_no varchar(6),
@start_time varchar(10),
@end_time varchar(10),
@area_type int
AS

create table #temp(
article_type_no int,
integration_no int,
score float
)
create table #redac
(
article_type_no int,
integration_no int,
score float
)

declare @center_server nvarchar(20)
declare @article_type_no int
declare @integration_no int
declare @old_article_type_no int
declare @score float
declare @flag bit
declare @sqlstr nvarchar(800)


select @center_server = rtrim(server_name) from subcenter where subcenter_area_no = '1'

---初始化数据首先将文章类型及综合标志对应的业绩置0
DECLARE init_temp CURSOR Local FOR
SELECT a.article_type_no, i.integration_no from integration_sign i,article_type a
WHERE a.article_type_no = i.article_type_no and a.area_type =@area_type

OPEN init_temp
IF @@error != 0 goto errorpos
IF @@CURSOR_ROWS = 0 GOTO ERRORPOS
set @flag = 0
FETCH NEXT FROM init_temp INTO

@article_type_no ,
@integration_no

------------------读入第一条记录--------------综合标志业绩初始化------------------------------------------------------------
if @@fetch_status = 0
begin
insert #temp (article_type_no,integration_no,score) values(@article_type_no, @integration_no, 0)
set @flag = 1
end
IF @@error != 0 goto errorpos
set @old_article_type_no = @article_type_no

WHILE @@FETCH_STATUS =0
BEGIN

FETCH NEXT FROM init_temp INTO
@article_type_no ,
@integration_no

IF(@old_article_type_no = @article_type_no)
begin
if @@fetch_status = 0
insert #temp (article_type_no,integration_no,score) values(@article_type_no, @integration_no, 0)
IF @@error != 0 goto errorpos
end
ELSE
begin
----------------对应文章的编译合计业绩-----------------------------------
insert #temp (article_type_no,integration_no,score) values(@old_article_type_no, -2000, 0)
insert #temp (article_type_no,integration_no,score) values(@article_type_no, @integration_no, 0)
IF @@error != 0 goto errorpos
SET @old_article_type_no = @article_type_no
end
END
if @flag = 1 insert #temp (article_type_no,integration_no,score) values(@old_article_type_no, -2000, 0)
CLOSE init_temp
DEALLOCATE init_temp
---------------------------初始化结束------------------------------------------------------------

-------------计算分类编辑业绩(按文章类别和综合标志)
delete from #redacset @sqlstr = N' select distinct article_type_no, integration_sign_no , sum(score) as score from '+@center_server+'link.mtsingle.dbo.section_achievement
where subcenter_no = '''+@subcenter_no+''' and (stat_date between '''+@start_time +'''and '''+@end_time+''')
group by article_type_no , integration_sign_no
order by article_type_no, integration_sign_no'
insert into #redac (article_type_no, integration_no, score)
exec(@sqlstr)
IF @@ERROR != 0 GOTO ERRORPOS

DECLARE redac_integration cursor Local FOR
select article_type_no ,integration_no, score from #redac
OPEN redac_integration

IF @@ERROR != 0 goto errorpos
IF @@CURSOR_ROWS = 0 GOTO ERRORPOS

FETCH NEXT FROM redac_integration INTO
@article_type_no,
@integration_no,
@score

if @@fetch_status = 0
UPDATE #temp SET score = @score WHERE article_type_no = @article_type_no and integration_no = @integration_no
IF @@ERROR != 0 goto errorpos
WHILE @@FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM redac_integration INTO
@article_type_no,
@integration_no,
@score
if @@fetch_status = 0
UPDATE #temp SET score = @score WHERE article_type_no = @article_type_no and integration_no = @integration_no
IF @@ERROR != 0 goto errorpos

END
--------计算某分中心对应的合计编辑业绩

DECLARE redac_total CURSOR Local FOR
select article_type_no , sum(score) as score from #redac group by article_type_no

OPEN redac_total
IF @@ERROR != 0 goto errorpos
IF @@CURSOR_ROWS = 0 GOTO ERRORPOS

FETCH NEXT FROM redac_total INTO
@article_type_no ,
@score

if @@fetch_status = 0
update #temp set score = @score where article_type_no = @article_type_no and integration_no = -2000

IF @@ERROR != 0 goto errorpos
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM redac_total INTO
@article_type_no,
@score
if @@fetch_status = 0
update #temp set score = @score where article_type_no = @article_type_no and integration_no = -2000
IF @@ERROR != 0 goto errorpos
END

CLOSE redac_total
DEALLOCATE redac_total

-------------合计编辑业绩计算结束
SELECT * FROM #temp
-----出错返回出口
errorpos:
return
GO
回复
zhangrongbo 2003-05-19
采用临时表试试
我以前也遇到这个问题是用临时表修改的,你看看这样合适吗?

CREATE PROCEDURE GetRemoteDeptScore
@subcenter_no varchar(6),
@start_time varchar(10),
@end_time varchar(10),
@area_type int
AS

create table #temp(
article_type_no int,
integration_no int,
score float
)
create table #redac
(
article_type_no int,
integration_no int,
score float
)

declare @center_server nvarchar(20)
declare @article_type_no int
declare @integration_no int
declare @old_article_type_no int
declare @score float
declare @flag bit
declare @sqlstr nvarchar(800)


select @center_server = rtrim(server_name) from subcenter where subcenter_area_no = '1'

---初始化数据首先将文章类型及综合标志对应的业绩置0
DECLARE init_temp CURSOR Local FOR
SELECT a.article_type_no, i.integration_no from integration_sign i,article_type a
WHERE a.article_type_no = i.article_type_no and a.area_type =@area_type

OPEN init_temp
IF @@error != 0 goto errorpos
IF @@CURSOR_ROWS = 0 GOTO ERRORPOS
set @flag = 0
FETCH NEXT FROM init_temp INTO

@article_type_no ,
@integration_no

------------------读入第一条记录--------------综合标志业绩初始化------------------------------------------------------------
if @@fetch_status = 0
begin
insert #temp (article_type_no,integration_no,score) values(@article_type_no, @integration_no, 0)
set @flag = 1
end
IF @@error != 0 goto errorpos
set @old_article_type_no = @article_type_no

WHILE @@FETCH_STATUS =0
BEGIN

FETCH NEXT FROM init_temp INTO
@article_type_no ,
@integration_no

IF(@old_article_type_no = @article_type_no)
begin
if @@fetch_status = 0
insert #temp (article_type_no,integration_no,score) values(@article_type_no, @integration_no, 0)
IF @@error != 0 goto errorpos
end
ELSE
begin
----------------对应文章的编译合计业绩-----------------------------------
insert #temp (article_type_no,integration_no,score) values(@old_article_type_no, -2000, 0)
insert #temp (article_type_no,integration_no,score) values(@article_type_no, @integration_no, 0)
IF @@error != 0 goto errorpos
SET @old_article_type_no = @article_type_no
end
END
if @flag = 1 insert #temp (article_type_no,integration_no,score) values(@old_article_type_no, -2000, 0)
CLOSE init_temp
DEALLOCATE init_temp
---------------------------初始化结束------------------------------------------------------------

-------------计算分类编辑业绩(按文章类别和综合标志)
delete from #redacset @sqlstr = N' select distinct article_type_no, integration_sign_no , sum(score) as score from '+@center_server+'link.mtsingle.dbo.section_achievement
where subcenter_no = '''+@subcenter_no+''' and (stat_date between '''+@start_time +'''and '''+@end_time+''')
group by article_type_no , integration_sign_no
order by article_type_no, integration_sign_no'
insert into #redac (article_type_no, integration_no, score)
exec(@sqlstr)
IF @@ERROR != 0 GOTO ERRORPOS

DECLARE redac_integration cursor Local FOR
select article_type_no ,integration_no, score from #redac
OPEN redac_integration

IF @@ERROR != 0 goto errorpos
IF @@CURSOR_ROWS = 0 GOTO ERRORPOS

FETCH NEXT FROM redac_integration INTO
@article_type_no,
@integration_no,
@score

if @@fetch_status = 0
UPDATE #temp SET score = @score WHERE article_type_no = @article_type_no and integration_no = @integration_no
IF @@ERROR != 0 goto errorpos
WHILE @@FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM redac_integration INTO
@article_type_no,
@integration_no,
@score
if @@fetch_status = 0
UPDATE #temp SET score = @score WHERE article_type_no = @article_type_no and integration_no = @integration_no
IF @@ERROR != 0 goto errorpos

END
--------计算某分中心对应的合计编辑业绩

DECLARE redac_total CURSOR Local FOR
select article_type_no , sum(score) as score from #redac group by article_type_no

OPEN redac_total
IF @@ERROR != 0 goto errorpos
IF @@CURSOR_ROWS = 0 GOTO ERRORPOS

FETCH NEXT FROM redac_total INTO
@article_type_no ,
@score

if @@fetch_status = 0
update #temp set score = @score where article_type_no = @article_type_no and integration_no = -2000

IF @@ERROR != 0 goto errorpos
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM redac_total INTO
@article_type_no,
@score
if @@fetch_status = 0
update #temp set score = @score where article_type_no = @article_type_no and integration_no = -2000
IF @@ERROR != 0 goto errorpos
END

CLOSE redac_total
DEALLOCATE redac_total

-------------合计编辑业绩计算结束
SELECT * FROM #temp
-----出错返回出口
errorpos:
return
GO
回复
BBQ 2003-05-19
可以考虑用sp_executesql执行
回复
caiyunxia 2003-05-19
上面是85
还有113
DECLARE redac_integration CURSOR LOCAL FOR
select (@sqlstr)
也有错
回复
caiyunxia 2003-05-19
如果需要如此,必须全部使用动态SQL
回复
caiyunxia 2003-05-19
DECLARE redac_total CURSOR LOCAL FOR
select @sqlstr
有错误
SELECT 不能如此使用

回复
fallstone 2003-05-19
DECLARE redac_total CURSOR LOCAL FOR
select @sqlstr
OPEN redac_total

这样就可以了么?不用执行@sqlstr?
偶是菜鸟,不懂,所以问问
回复
Rewiah 2003-05-19
加了个空格,慢慢找
或者拷贝过去,实在不应该是这个问题

太长了
回复
pengdali 2003-05-19
CREATE PROCEDURE GetRemoteDeptScore
@subcenter_no varchar(6),
@start_time varchar(10),
@end_time varchar(10),
@area_type int
AS

create table #temp(
article_type_no int,
integration_no int,
score float
)

declare @center_server nvarchar(20)
declare @article_type_no int
declare @integration_no int
declare @old_article_type_no int
declare @score float
declare @flag bit
declare @sqlstr nvarchar(255)


select @center_server = rtrim(server_name) from subcenter where subcenter_area_no = '1'

---初始化数据首先将文章类型及综合标志对应的业绩置0
DECLARE init_temp CURSOR LOCAL FOR
SELECT a.article_type_no, i.integration_no from integration_sign i,article_type a
WHERE a.article_type_no = i.article_type_no and a.area_type =@area_type

OPEN init_temp
IF @@error != 0 goto errorpos
IF @@CURSOR_ROWS = 0 GOTO ERRORPOS
set @flag = 0
FETCH NEXT FROM init_temp INTO

@article_type_no ,
@integration_no

------------------读入第一条记录--------------综合标志业绩初始化------------------------------------------------------------
if @@fetch_status = 0
begin
insert #temp (article_type_no,integration_no,score) values(@article_type_no, @integration_no, 0)
set @flag = 1
end
IF @@error != 0 goto errorpos
set @old_article_type_no = @article_type_no

WHILE @@FETCH_STATUS =0
BEGIN

FETCH NEXT FROM init_temp INTO
@article_type_no ,
@integration_no

IF(@old_article_type_no = @article_type_no)
begin
if @@fetch_status = 0
insert #temp (article_type_no,integration_no,score) values(@article_type_no, @integration_no, 0)
IF @@error != 0 goto errorpos
end
ELSE
begin
----------------对应文章的编译人合计业绩-----------------------------------
insert #temp (article_type_no,integration_no,score) values(@old_article_type_no, -2000, 0)
insert #temp (article_type_no,integration_no,score) values(@article_type_no, @integration_no, 0)
IF @@error != 0 goto errorpos
SET @old_article_type_no = @article_type_no
end
END
if @flag = 1 insert #temp (article_type_no,integration_no,score) values(@old_article_type_no, -2000, 0)
CLOSE init_temp
DEALLOCATE init_temp
---------------------------初始化结束------------------------------------------------------------
--------计算某分中心对应的合计编辑业绩

set @sqlstr =N'DECLARE redac_total CURSOR LOCAL FOR select distinct article_type_no, sum(score) score from '+@center_server+'link.mtsingle.dbo.section_achievement
where subcenter_no = '''+@subcenter_no +''' and (stat_date between '''+@start_time +'''and''' +@end_time+''')
group by article_type_no '
--出错位置

exec(@sqlstr)
OPEN redac_total
IF @@ERROR != 0 goto errorpos
IF @@CURSOR_ROWS = 0 GOTO ERRORPOS
FETCH NEXT FROM redac_total INTO
@article_type_no,
@score
if @@fetch_status = 0
update #temp set score = @score where article_type_no = @article_type_no and integration_no = -2000
IF @@ERROR != 0 goto errorpos
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM redac INTO
@article_type_no,
@score
if @@fetch_status = 0
update #temp set score = @score where article_type_no = @article_type_no and integration_no = -2000
IF @@ERROR != 0 goto errorpos
END

CLOSE redac_total
DEALLOCATE redac_total

-------------合计编辑业绩计算结束
-------------计算分类编辑业绩(按文章类别和综合标志)

set @sqlstr = N'DECLARE redac_integration CURSOR LOCAL FOR select distinct article_type_no,integration_sign_no ,sum(score) score from '+@center_server+'link.mtsingle.dbo.section_achievement
where subcenter_no = '''+@subcenter_no+''' and (stat_date between '''+@start_time +'''and '''+@end_time+''')
group by article_type_no , integration_sign_no
order by article_type_no, integration_sign_no'
--------------------------print @sqlstr

exec(@sqlstr)

OPEN redac_integration
IF @@ERROR != 0 goto errorpos
IF @@CURSOR_ROWS = 0 GOTO ERRORPOS
FETCH NEXT FROM redac_integration INTO
@article_type_no,
@integration_no,
@score
if @@fetch_status = 0
UPDATE #temp SET score = @score WHERE article_type_no = @article_type_no and integration_no = @integration_no
IF @@ERROR != 0 goto errorpos
WHILE @@FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM redac_integration INTO
@article_type_no,
@integration_no,
@score
if @@fetch_status = 0
UPDATE #temp SET score = @score WHERE article_type_no = @article_type_no and integration_no = @integration_no
IF @@ERROR != 0 goto errorpos

END

SELECT * FROM #temp
RETURN
-----出错返回出口
errorpos:
SELECT * FROM #temp
return
GO
回复
Rewiah 2003-05-19
set @sqlstr =N' select distinct article_type_no, sum(score) score from '+@center_server+'link.mtsingle.dbo.section_achievement
where subcenter_no = '''+@subcenter_no +''' and (stat_date between '''+@start_time +'''and''' +@end_time+''')
group by article_type_no '

-->

set @sqlstr =N' select distinct article_type_no, sum(score) score from '+@center_server+'link.mtsingle.dbo.section_achievement
where subcenter_no = '''+@subcenter_no +''' and (stat_date between '''+@start_time +'''and ''' +@end_time+''')
group by article_type_no '

??
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2003-05-19 08:35
社区公告
暂无公告