选择sp_executesql如何

Question

请。我需要运行存储在临时表中的存储过程。我有两个表：

create table pruebaa
(
    cam1 integer primary key,
    cam2 varchar(20)
)
insert into pruebaa (cam1, cam2) values (1, 'valor1');
insert into pruebaa (cam1, cam2) values (2, 'valor2');
insert into pruebaa (cam1, cam2) values (3, 'valor3');
insert into pruebaa (cam1, cam2) values (4, 'valor4');
insert into pruebaa (cam1, cam2) values (5, 'valor5');
insert into pruebaa (cam1, cam2) values (6, 'valor6');
create table pruebab
(
    cam1 integer primary key,
    cam2 varchar(20)
)

我需要将数据表A保存到表B，但用于存储过程。我这样做了：

create procedure insertapruebab(@cam1_ex int, @cam2_ex varchar(20))
as
begin
    insert into pruebab(cam1, cam2) values (@cam1_ex, @cam2_ex);
end
create table #querysEjecutar(
    campo1 varchar(2000)
)
insert into #querysEjecutar(campo1) (select ' exec insertapruebab '+ CONVERT(varchar(20), cam1)  + ', ' + CONVERT(varchar(20), cam2) from pruebaa);
declare @campoquery nvarchar(2000);
set @campoquery = 'select campo1 from #querysEjecutar';
exec sp_executesql @campoquery;
select * from pruebab;

但在"exec sp_executesql"行中，结果是：

 exec insertapruebab 1, valor1
 exec insertapruebab 2, valor2
 exec insertapruebab 3, valor3
 exec insertapruebab 4, valor4
 exec insertapruebab 5, valor5
 exec insertapruebab 6, valor6

谢谢

Answer 1

您可以将动态 SQL 替换为以下脚本：

  declare @Cam1Counter int
  select @Cam1Counter = min(cam1) from pruebaa
  while @Cam1Counter > 0
  begin
     declare @cam2Value varchar(20)
     select @cam2Value = cam2 from pruebaa where cam1 = @Cam1Counter
     exec insertapruebab @Cam1Counter, @cam2Value
     select @Cam1Counter = min(cam1) from pruebaa where cam1 > @Cam1Counter
  end

这基本上将为 pruebaa 表中的每一行调用 insertapruebab。