insert variable no. of rows in sql using store procedure

Question

5.00/5 (1 vote)

See more:

SQL-Server-2005

I want to write store procedure for below task

SQL

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

Aarti Meswania

Add a Solution

Comments

__TR__ 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 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__ 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 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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

kankeyan · Answer 1 · 2013-01-04T00:52:00

Solution 1

SQL

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

Posted 4-Jan-13 0:52am

kankeyan

Comments

kankeyan 4-Jan-13 6:53am

BY using the above procedure u need to call multiple times to Insert the record

Aarti Meswania 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 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 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 4-Jan-13 7:13am

_TR_ has suggested me a way to use table valued parameter, and he is right it's feasible