delphi中用adoquery创建存储过程错误，高手快来帮帮忙啊！

Question

错误提示好像是'出错。CREATE PROCEDURE [player_bh_and_insert_set] 
   @select_list int
AS
set nocount on
   declare @bh  int
     declare @sql_str varchar(100)
   if @select_list=1 
      goto update_sql
   else if @select_list=2 or @select_list=3
         goto   inset_into_sql 
      else
        return 0
 update_sql:
           declare @len int
           declare @id   int 
           declare @sql  cursor
           set @sql=cursor for select Unit_bh_size  from var_set
           open @sql
           fetch next from @sql into @len
           close @sql
        deallocate @sql
         set @sql=cursor for select player.id from units,player where units.id=player.player_unit_id order by units.unit_bh, player.id
          open @sql
         set @bh=1   
          declare @count int   
          set @count=1
         fetch next from @sql into @id
       while (@@fetch_status=0)
           begin
             set @count=1
              set @sql_str='update player set player_bh='''
                while  (@count<=@len-len(@bh))
                    begin 
                         set @sql_str=@sql_str+'0'
                          set  @count=@count+1
                     end
             set   @sql_str=@sql_str+convert(varchar,@bh)+'''  '+' where id='+convert(varchar,@id)  
          exec(@sql_str)
         set @bh=@bh+1
         fetch next from @sql into @id   
         end           
   close @sql
        deallocate @sql  
     return 0
 inset_into_sql:
    declare @table_name varchar(20)
    declare @field varchar(20)
    declare @Player_subject_count int   
    declare @i int
    declare @inset_sql_str varchar(2000)
    select @Player_subject_count =player_join_subject_count from var_set 
   if @select_list=2 
     begin
           set @table_name='player'
            set      @field='player.ID'
    end
   else if @select_list=3
     begin
           set  @table_name='unit_bh'
           set @field='unit_bh.unit_id'
          set @Player_subject_count=1
     end        
    set @i=1  if @player_subject_count=0 
         return 0
   else
     begin
          if @select_list=2 
               exec('delete from  player_join_subject where player_id in (select id from  player)')
          else 
               if @select_list=3
                 exec('delete from  player_join_subject  where player_id in (select unit_id from UNIT_BH)')
               while (@i<=@Player_subject_count)
                    begin
                              set @inset_sql_str='insert into player_join_subject(player_ID,join_subject_name)  select '+ @field+',zubei_wf.zubei_name+subject_wf.subject_name+''预赛'''+'  from ' + @table_name+ ' inner join subject_wf on '+@table_name+'.subject_id'+convert(char(2),@i)+'=subject_wf.subject_id inner join zubei_wf on ' + @table_name+'.zubei_id=zubei_wf.zubei_id where subject_wf.subject_name is not null and subject_wf.subject_times=''是'''
                               exec(@inset_sql_str)                
                              set @inset_sql_str='insert into player_join_subject(player_ID,join_subject_name)  select '+ @field+',zubei_wf.zubei_name+subject_wf.subject_name+''决赛'''+'  from ' + @table_name+ ' inner join subject_wf on '+@table_name+'.subject_id'+convert(char(2),@i)+'=subject_wf.subject_id inner join zubei_wf on ' + @table_name+'.zubei_id=zubei_wf.zubei_id where subject_wf.subject_name is not null and subject_wf.subject_times=''否'''
                               exec(@inset_sql_str)   
                            set @i=@i+1
                    end
    end
   return 0
GO

Answer 1

把最后一行GO去掉因为GO不是SQL的关键字，通过ADOQuery连接不通过！
如果你的存储过程在SQL管理器中通过，删掉GO后即可通过ADOQuery创建（当然，你得检测当前数据库中没有这个存储过程）

Answer 2

去掉go后运行出现："Parameter object is improperly defined. Inconsistent or imcomplete information was provided."错误提示.

Answer 3

最后一行GO去掉
还是会出现"Parameter object is improperly defined. Inconsistent or imcomplete information was provided."的错误 提示，这是什么意思啊

Answer 4

我现在只能说，你的存储过程设计的有问题
看这几句：
update_sql:
inset_into_sql:
等等，因为Delphi默认的函数是以:开头，而SQL是以:作为跳转的标志
所以你用Delphi的ADOQuery打开时会出现找不到参数的问题，其实本来就不应该有参数......
也就是Parameter object is improperly defined. Inconsistent or imcomplete information was provided.的错误提示重新设计存储过程吧，依然要注意:及GO的问题，有些必须使用:的地方用SQL中的CHAR()函数代替！

Answer 5

写错了
等等，因为Delphi默认的函数是以:开头，而SQL是以:作为跳转的标志
应该是：
等等，因为Delphi默认的参数是以:开头，而SQL是以:作为跳转的标志后面的改过来了

Answer 6

这么长？建议你调用osql执行得了

Answer 7

把语句结构改一下，不用跳转就行了吗
CREATE PROCEDURE [player_bh_and_insert_set] 
   @select_list int
AS
set nocount on
   declare @bh  int
     declare @sql_str varchar(100)
   if @select_list=1 
   begin
           declare @len int
           declare @id   int 
           declare @sql  cursor
           set @sql=cursor for select Unit_bh_size  from var_set
           open @sql
           fetch next from @sql into @len
           close @sql
        deallocate @sql
         set @sql=cursor for select player.id from units,player where units.id=player.player_unit_id order by units.unit_bh, player.id
          open @sql
         set @bh=1   
          declare @count int    --次数
          set @count=1
         fetch next from @sql into @id
       while (@@fetch_status=0)
           begin
             set @count=1
              set @sql_str='update player set player_bh='''
                while  (@count<=@len-len(@bh))
                    begin 
                         set @sql_str=@sql_str+'0'
                          set  @count=@count+1
                     end
             set   @sql_str=@sql_str+convert(varchar,@bh)+'''  '+' where id='+convert(varchar,@id)  
          exec(@sql_str)
         set @bh=@bh+1
         fetch next from @sql into @id   
         end           
       close @sql
        deallocate @sql  
     return 0
   end
   else if @select_list=2 or @select_list=3
   begin
    declare @table_name varchar(20)
    declare @field varchar(20)
    declare @Player_subject_count int   --每名运动员限报人数 
    declare @i int
    declare @inset_sql_str varchar(2000)
    select @Player_subject_count =player_join_subject_count from var_set 
   if @select_list=2 
     begin
           set @table_name='player'
            set      @field='player.ID'
    end
   else if @select_list=3
     begin
           set  @table_name='unit_bh'
           set @field='unit_bh.unit_id'
          set @Player_subject_count=1
     end        
    set @i=1  if @player_subject_count=0 
         return 0
   else
     begin
          if @select_list=2 
               exec('delete from  player_join_subject where player_id in (select id from  player)')
          else 
               if @select_list=3
                 exec('delete from  player_join_subject  where player_id in (select unit_id from UNIT_BH)')
               while (@i<=@Player_subject_count)
                    begin
                              set @inset_sql_str='insert into player_join_subject(player_ID,join_subject_name)  select '+ @field+',zubei_wf.zubei_name+subject_wf.subject_name+''预赛'''+'  from ' + 
@table_name+ ' inner join subject_wf on '+@table_name+'.subject_id'+convert(char(2),@i)+'=subject_wf.subject_id inner join zubei_wf on ' + @table_name+'.zubei_id=zubei_wf.zubei_id where 
subject_wf.subject_name is not null and subject_wf.subject_times=''是'''
                               exec(@inset_sql_str)                
                              set @inset_sql_str='insert into player_join_subject(player_ID,join_subject_name)  select '+ @field+',zubei_wf.zubei_name+subject_wf.subject_name+''决赛'''+'  from ' + 
@table_name+ ' inner join subject_wf on '+@table_name+'.subject_id'+convert(char(2),@i)+'=subject_wf.subject_id inner join zubei_wf on ' + @table_name+'.zubei_id=zubei_wf.zubei_id where 
subject_wf.subject_name is not null and subject_wf.subject_times=''否'''
                               exec(@inset_sql_str)   
                            set @i=@i+1
                    end
    end
   return 0
   end
      else
        return 0