Click here to Skip to main content
11,648,094 members (60,390 online)
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 jyo.net 500
1 F-ES Sitecore 370
2 DamithSL 300
3 OriginalGriff 277
4 CPallini 250
0 OriginalGriff 1,342
1 jyo.net 994
2 DamithSL 981
3 Sergey Alexandrovich Kryukov 893
4 CPallini 845


Advertise | Privacy | Mobile
Web04 | 2.8.150804.4 | Last Updated 4 Jan 2013
Copyright © CodeProject, 1999-2015
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