Am I doing this correctly? 
Author Message
 Am I doing this correctly?

Hi,

I am writing a stored procedure where I want to wrap an insert
statement that inserts several rows into a table in a transaction so
that if we have a problem, all the inserts are rolled back.  Is this
the correct way of doing it?


BEGIN




    RAISERROR('Sending mee tessu sendingarnmeri er tegar
skr.',16,1)
ELSE
  BEGIN tran
    INSERT INTO adfarir_logg(SendingNr, MalNr, Dags, Knt, Nafn)

    FROM lanstraust
    WHERE lanstraust.MalNr not in (SELECT MalNr FROM adfarir_logg)
  COMMIT        
END
GO

Thanks,
?gir



Fri, 08 Apr 2005 18:47:57 GMT
 Am I doing this correctly?

?gir,

You don't need BEGIN TRAN and COMMIT TRAN for this. The INSERT statement is an atomic operation.
One of the advantages with expressing an operation as a single statement, btw.

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...


Quote:
> Hi,

> I am writing a stored procedure where I want to wrap an insert
> statement that inserts several rows into a table in a transaction so
> that if we have a problem, all the inserts are rolled back.  Is this
> the correct way of doing it?


> BEGIN




>     RAISERROR('Sending mee tessu sendingarnmeri er tegar
> skr.',16,1)
> ELSE
>   BEGIN tran
>     INSERT INTO adfarir_logg(SendingNr, MalNr, Dags, Knt, Nafn)

>     FROM lanstraust
>     WHERE lanstraust.MalNr not in (SELECT MalNr FROM adfarir_logg)
>   COMMIT
> END
> GO

> Thanks,
> ?gir



Fri, 08 Apr 2005 19:36:19 GMT
 Am I doing this correctly?
Sveinsson

Without addressing your SP specifically, this is how I typically structure
these:

create proc ssp_transtest

as

    set <transaction isolation level if required> <deadlock_priority if
required>
    begin transaction

        INSERT <blah>
            < Note this could be a loop of inserts as per your SP, as long
as you



    commit transaction
    return

Error_Line:


HTH

R.

--
--
Kind Regards,

Robert A. Ellis, MCSD
Software Developer


Quote:
> Hi,

> I am writing a stored procedure where I want to wrap an insert
> statement that inserts several rows into a table in a transaction so
> that if we have a problem, all the inserts are rolled back.  Is this
> the correct way of doing it?


> BEGIN




>     RAISERROR('Sending mee tessu sendingarnmeri er tegar
> skr.',16,1)
> ELSE
>   BEGIN tran
>     INSERT INTO adfarir_logg(SendingNr, MalNr, Dags, Knt, Nafn)

>     FROM lanstraust
>     WHERE lanstraust.MalNr not in (SELECT MalNr FROM adfarir_logg)
>   COMMIT
> END
> GO

> Thanks,
> ?gir



Fri, 08 Apr 2005 19:43:11 GMT
 Am I doing this correctly?
Sorry I completely negligently misread that post.
    Yours,
        Idiot brain.

--
--
Kind Regards,

Robert A. Ellis, MCSD
Software Developer


Quote:
> Hi,

> I am writing a stored procedure where I want to wrap an insert
> statement that inserts several rows into a table in a transaction so
> that if we have a problem, all the inserts are rolled back.  Is this
> the correct way of doing it?


> BEGIN




>     RAISERROR('Sending mee tessu sendingarnmeri er tegar
> skr.',16,1)
> ELSE
>   BEGIN tran
>     INSERT INTO adfarir_logg(SendingNr, MalNr, Dags, Knt, Nafn)

>     FROM lanstraust
>     WHERE lanstraust.MalNr not in (SELECT MalNr FROM adfarir_logg)
>   COMMIT
> END
> GO

> Thanks,
> ?gir



Fri, 08 Apr 2005 19:49:49 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. What am I forgetting or doing wrong

2. Am I doing this the best way?

3. what am I doing wrong in this procedure?

4. TSQL question - what am I doing wrong?

5. what am i doing wrong?

6. Simple one what am i doing wrong

7. Darn what am i doing wrong?

8. What am I doing wrong

9. What am I doing wrong?

10. Joins - What I'am doing wrong

11. SQL problem, what am I doing wong?

12. I think I am doing something wrong


 
Powered by phpBB® Forum Software