I have a .SP that receives several parameters these come as follows
@i_parametro1 = '0123','0125','0255','0245','2547'
@i_parametro2 = '0123','0125','0255','0245','2547'
@i_parametro3 = '0123','0125','0255','0245','2547'
@i_parametro4 = '0123','0125','0255','0245','2547'
..... and so on up to @ i_parameter20 this depends on what was sent, but at most it will always be 20 and at least 1.
Then I have to fill in a temporary table with the following query:
insert ca_operaciones_excel_tmp
select
op_operacion, op_moneda, op_fecha_ini,
op_lin_credito, op_estado, op_migrada,
op_toperacion , op_oficina, op_oficial,
op_cliente , op_tramite, op_banco,
op_fecha_reajuste,op_tipo, op_reajuste_especial,
op_reajustable, op_monto, op_monto_aprobado,
op_anterior,@s_user
from ca_operacion
where op_banco in (@i_parametro1)
and op_estado not in (0,11,99,3)
the problem is that I need the query to be dynamic, that is, replace the value that is in the where with the other parameters @ i_parameter1 @ i_parameter2 @ i_parametro3 @ i_parametro4 ....
@w_siguiente varchar (20)
@w_contador int
Try to go through it with the following while:
while @w_contador < = 19
begin
select @w_siguiente = '@operacion'+ CONVERT (varchar,@w_contador)
end
and in the query step:
where op_banco in ('+ @w_siguiente +')
However when I try to execute, it takes me is the literal text, not the value of the variable.
this is the complete code
while @w_contador < = 19
begin
select @w_siguiente = '@operacion'+ CONVERT (varchar,@w_contador)
insert ca_operaciones_excel_tmp
select
op_operacion, op_moneda, op_fecha_ini,
op_lin_credito, op_estado, op_migrada,
op_toperacion , op_oficina, op_oficial,
op_cliente , op_tramite, op_banco,
op_fecha_reajuste,op_tipo, op_reajuste_especial,
op_reajustable, op_monto, op_monto_aprobado,
op_anterior,@s_user
from ca_operacion
where op_banco in (@w_siguiente)
and op_estado not in (0,11,99,3)
if @@rowcount = 0
begin
select @w_error = 1
goto ERROR
end
end
How can I fix it.