插入变量号。使用存储过程的sql中的行数 [英] insert variable no. of rows in sql using store procedure

问题描述

我想为以下任务编写商店程序

I want to write store procedure for below task

begin transaction t1
    Begin try
    
        delete from ProdDtl where vouno=1
        --HERE I WILL UPDATE RECORD BY DELETE THEN INSERT, BECAUSE NO. OF DATA-ROWS ARE NOT FIXED... IT IS COMING FROM GRID-VIEW.
        insert into ProdDtl
             select 1 as vouno, 1 as prodId, 200 as prodAmt
        insert into ProdDtl
             select 1 as vouno, 3 as prodId, 200 as prodAmt
        insert into ProdDtl
             select 1 as vouno, 9 as prodId, 200 as prodAmt
    
    End try
    Begin catch
       Rollback transaction t1
    End catch
commit transaction t1

注意：我已经写了3个插入查询，但它可能会有所不同取决于gridview中的用户输入行

我想要这个senerio的存储过程

Note: I have write 3 insert queries but it can vary depends on user input rows in gridview
and I want store procedure for this senerio

推荐答案

Create Procedure sp_proddtl

@vouno int,
@prodid int,
@prodamt float
AS

BEGIN TRANSACTION T1
    BEGIN TRY
    
        DELETE FROM ProdDtl WHERE vouno=1
        --HERE I WILL UPDATE RECORD BY DELETE THEN INSERT, BECAUSE NO. OF DATA-ROWS ARE NOT FIXED... IT IS COMING FROM GRID-VIEW.
       
        --     select 1 as vouno, 1 as prodId, 200 as prodAmt
        --insert into ProdDtl
        --     select 1 as vouno, 3 as prodId, 200 as prodAmt
        --insert into ProdDtl
        --     select 1 as vouno, 9 as prodId, 200 as prodAmt
    
		 INSERT INTO ProdDtl VALUES(@vouno,@prodid ,@prodamt )
    
    
    END TRY
    BEGIN CATCH
       ROLLBACK TRANSACTION T1
    END CATCH
COMMIT TRANSACTION T1

这篇关于插入变量号。使用存储过程的sql中的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！