How sp_executesql with select?

Question

Please. I need to run a stored procedure that is stored in a temporary table. I have two tables:

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)
)

I need save the data table A to table B but for stored procedure. I did this:

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;

But in the line "exec sp_executesql" the result is:

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

Thanks

You're literally executing: select campo1 from #querysEjecutar which is just going to return all the exec statements but not actually execute them. You need to create all those statements into one string and then pass that to sp_executesql. — JeffO, Apr 27 '16 at 18:30

Vasanth · Answer 1 · 2016-04-28T13:41:15.743

0

You can replace the dynamic SQL with below script:

  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

This will basically call insertapruebab for every row in pruebaa table.

edited Apr 28 '16 at 13:41

answered Apr 27 '16 at 18:10

Vasanth

1,670
1
13
17

if this can be done in a simpler way . But I need you to do so . – Santiago Madrid Apr 27 '16 at 18:19
Simpler way is provided in my answer. Look at the first query in the answer. – Vasanth Apr 27 '16 at 18:42
Unfortunately it does not work . Not get to run the stored procedure. I need to run stored procedure dynamically to be more accurate , but I can not run the query that is stored in the temporary table. – Santiago Madrid Apr 27 '16 at 20:02
Updated answer to call the proc instead of direct insert. – Vasanth Apr 28 '16 at 13:41

How sp_executesql with select?

1 Answers1