Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005 SQL-Server , +
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
 
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
Posted 4-Jan-13 0:43am
Comments
__TR__ at 4-Jan-13 6:51am
   
Try looping through the gridview rows and call the stored procedure inside the loop. Your stored procedure will have only one insert statement but will be called multiple times depending on the number of rows in the gridview.
Aarti Meswania at 4-Jan-13 6:53am
   
yes I am already doing so...
 
but what I want is, It should be in single transaction
 
and if a single row of that all fire exception then the transaction should be rollback
__TR__ at 4-Jan-13 7:03am
   
One approach would be to insert data from gridview to a staging table. Once all the records are inserted into the staging table, insert the data from staging table to your final table. So if there is any error while inserting data into staging table no data gets inserted into the final table and if any error occurs while inserting data from staging to final table, it can be rolled back.
Aarti Meswania at 4-Jan-13 7:13am
   
thank you! for suggesion
 
you are right, I have seen an example for your suggested way, on this link
http://www.mssqltips.com/sqlservertip/2112/table-value-parameters-in-sql-server-2008-and-net-c/
 
:)

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  
Comments
kankeyan at 4-Jan-13 6:53am
   
BY using the above procedure u need to call multiple times to Insert the record
Aarti Meswania at 4-Jan-13 6:54am
   
yes I am already doing so...
 
but what I want is, It should be in single transaction
 
and if a single row of that all rows fail to be saved then the whole transaction should be rollback
kankeyan at 4-Jan-13 6:58am
   
Then use a Begin transaction and Rollback Transaction in two different Sp's you can try to insert a multiple rows by calling multiple times, once error found in particular record call rollback Sp if not then Call Commit Sp.
Aarti Meswania at 4-Jan-13 7:08am
   
please refer your suggestion again because it will not follow sql injection rules
you mean to say
it's a query as below...
"begin transaction t1
exec spnm param11,parm12...
exec spnm param21,param22...
commit transaction t1"
Aarti Meswania at 4-Jan-13 7:13am
   
_TR_ has suggested me a way to use table valued parameter, and he is right it's feasible

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 354
1 Richard MacCutchan 310
2 OriginalGriff 275
3 Vinay Mistry 163
4 Jared Sanow 145
0 Sergey Alexandrovich Kryukov 5,939
1 OriginalGriff 4,885
2 CPallini 2,473
3 Abhinav S 1,507
4 Richard MacCutchan 1,437


Advertise | Privacy | Mobile
Web03 | 2.8.140814.1 | Last Updated 4 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100