关于动态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
...全文
40 11 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
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 '

??
基础类 - SQL语句
基础类 - SQL语句 <br />转载请标明出处： http://blog.csdn.net/hz/archive/2006/03/01/613376.asp 本文来自 CSDN 博客。x sql server 怎么跨库查询？ 如何判断字符的大小写 ? 如何...
SQl语句学习专题(转)
晴天 发表于 2004-10-26 11:16:00 ...SQL语句集锦 --语 句功 能 --数据操作 SELECT--从数据库表中检索数据行和列 INSERT--向数据库表添加新数据行 DELETE--从数据库表中删除数据行 UPDATE...
MS-SQL Server 基础类 - SQL语句
各位大虾请帮忙，在线等待！！！！！！！ 关于重复记录的查询！！！十万火急！ 请教一个关于distinct的SQL语句该怎么写 SQL语句--找出相同字段中的记录集中,另一字段为最大值的那条记录 请教一个树形表的SQL交叉表...
SQLSERVER完全优化教程
1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 　我们把这种 正文内容 本身就是一种按照一定规则排列的 目录 称为“聚集索引”。 　需要两个过程，先找到目录中的结果，然后再翻到您所...
「Sqlserver」数据分析师有理由爱Sqlserver之三-最值得使有低投入高产出的Sqlserver功能...
数据分析师群体，有别于一般的传统开发群体和数据库运维群体，对Sqlserver的功能需求上也各不相同，本篇以笔者的亲身经历，用一种有别于一般性教程的角度来讲解Sqlser...
MS-SQL Server

34,838

社区成员

254,632

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章