我有一个存储过程,其中insert查询在两个表上触发。就像你在这里看到的,@Value1,@Value2,@Value3,@Value4有参数。我已经为每个块编写了insert语句,我是否可以在这里使用循环来避免每个块的多次insert写入,假设我有第n个值,那么我必须编写第n个按块插入的查询,有没有更好的方法来完成这个任务
Create proc sp_insert_master_child
(
@Details_Data custom_tb READONLY,
@Value1 nvarchar(500),
@Value2 nvarchar(500),
@Value3 nvarchar(500),
@Value4 nvarchar(500),
@Value5 nvarchar(500),
@Value6 nvarchar(500),
@Value7 nvarchar(500)
)
as begin
declare @id bigint
------- block 1
if @Value1 is not null
begin
insert into masters_table (col1,col2,col3))
select Substring(@Value1, 1,Charindex(',', @Value1)-1) as col_value_1,
Substring(@Value1, Charindex(',', @Value1)+1, LEN(@Value1)) as col_value_2
select @id = @@IDENTITY
insert into child_table (col1,col2,col3)
SELECT @id,col_val_2,col_val_3 FROM @Details_Data WHERE blockType='blk_1';
end
------ block 2
if @Value2 is not null
begin
insert into masters_table (col1,col2,col3))
select Substring(@Value2, 1,Charindex(',', @Value2)-1) as col_value_1,
Substring(@Value2, Charindex(',', @Value2)+1, LEN(@Value2)) as col_value_2
select @id = @@IDENTITY
insert into child_table (col1,col2,col3)
SELECT @id,col_val_2,col_val_3 FROM @Details_Data WHERE blockType='blk_2';
end
------ block 3
if @Value3 is not null
begin
insert into masters_table (col1,col2,col3))
select Substring(@Value3, 1,Charindex(',', @Value3)-1) as col_value_1,
Substring(@Value3, Charindex(',', @Value2)+1, LEN(@Value3)) as col_value_2
select @id = @@IDENTITY
insert into child_table (col1,col2,col3)
SELECT @id,col_val_2,col_val_3 FROM @Details_Data WHERE blockType='blk_3';
end
------ block 4
.......
------ block 5
....... 发布于 2014-07-02 17:59:56
您可以将所有参数插入到表.Use while循环中,并从表中逐行(参数)读取。
例如:
declare @x int
set @x= 1
declare @tmp table ( val int )
while (@x < noofrows)
begin
***********
Use Top @x value to get row(parameter in your case) from table
perform your operation
*************
increment x(set @X=@x+1)
endhttps://stackoverflow.com/questions/24527857
复制相似问题