关于动态SQL的问题，请高手帮忙，分不够再加（在线等待）

Question

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 '??

Answer 1

CREATE PROCEDURE GetRemoteDeptScore 
              @subcenter_no varchar(6),
              @start_time  varchar(10),
              @end_time  varchar(10),
              @area_type int
AScreate 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_typeOPEN 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 
ENDCLOSE 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 @sqlstrexec(@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  errorposENDSELECT * FROM #temp
RETURN 
-----出错返回出口 
errorpos:
  SELECT * FROM #temp
  return
GO

Answer 2

DECLARE  redac_total CURSOR LOCAL FOR
select @sqlstr
OPEN redac_total这样就可以了么？不用执行@sqlstr？
偶是菜鸟，不懂，所以问问

Answer 3

DECLARE  redac_total CURSOR LOCAL FOR
select @sqlstr
有错误
SELECT 不能如此使用

Answer 4

如果需要如此，必须全部使用动态SQL

Answer 5

上面是85
还有113
DECLARE  redac_integration CURSOR LOCAL FOR
select (@sqlstr)
也有错

Answer 6

采用临时表试试
我以前也遇到这个问题是用临时表修改的，你看看这样合适吗？CREATE PROCEDURE GetRemoteDeptScore 
              @subcenter_no varchar(6),
              @start_time  varchar(10),
              @end_time  varchar(10),
              @area_type int
AScreate 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_typeOPEN 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 ERRORPOSDECLARE  redac_integration cursor  Local FOR
select article_type_no ,integration_no, score  from #redac
OPEN redac_integrationIF @@ERROR != 0 goto  errorpos
IF @@CURSOR_ROWS = 0  GOTO ERRORPOSFETCH NEXT FROM  redac_integration INTO
           @article_type_no,
           @integration_no,
           @scoreif @@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  errorposEND
--------计算某分中心对应的合计编辑业绩DECLARE  redac_total CURSOR Local  FOR                          
        select  article_type_no , sum(score) as score  from #redac  group by article_type_noOPEN redac_total
IF @@ERROR != 0 goto  errorpos
IF @@CURSOR_ROWS = 0  GOTO ERRORPOSFETCH NEXT FROM redac_total INTO
      @article_type_no     ,
      @scoreif @@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 
ENDCLOSE redac_total 
DEALLOCATE redac_total-------------合计编辑业绩计算结束
SELECT * FROM #temp
-----出错返回出口 
errorpos:
  return
GO

Answer 7

CREATE PROCEDURE GetRemoteDeptScore 
              @subcenter_no varchar(6),
              @start_time  varchar(10),
              @end_time  varchar(10),
              @area_type int
AScreate 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_typeOPEN 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 ERRORPOSDECLARE  redac_integration cursor  Local FOR
select article_type_no ,integration_no, score  from #redac
OPEN redac_integrationIF @@ERROR != 0 goto  errorpos
IF @@CURSOR_ROWS = 0  GOTO ERRORPOSFETCH NEXT FROM  redac_integration INTO
           @article_type_no,
           @integration_no,
           @scoreif @@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  errorposEND
--------计算某分中心对应的合计编辑业绩DECLARE  redac_total CURSOR Local  FOR                          
        select  article_type_no , sum(score) as score  from #redac  group by article_type_noOPEN redac_total
IF @@ERROR != 0 goto  errorpos
IF @@CURSOR_ROWS = 0  GOTO ERRORPOSFETCH NEXT FROM redac_total INTO
      @article_type_no     ,
      @scoreif @@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 
ENDCLOSE redac_total 
DEALLOCATE redac_total-------------合计编辑业绩计算结束
SELECT * FROM #temp
-----出错返回出口 
errorpos:
  return
GO