|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Run 2nd SQL in stored proctable from a source table (PayTotals). After that is successful, I would like to run a delete statement "DELETE FROM dbo.PayTotals WHERE PaidYr = @PaidYr" but only if the previous INSERT is successful. What would I need to add to the proc below to make that happen? Thanks. David CREATE PROCEDURE [mc_inshstPayTotals] (@PaidYr [smallint]) AS INSERT INTO dbo.hstPayTotals ([EmployerCaseNumber], [EmployerMax], [PayFirst], [PaySecond], [PaidYr], [PaidMo]) SELECT EmployerCaseNumber, EmployerMax, PayFirst, PaySecond, PaidYr, PaidMo FROM dbo.PayTotals WHERE PaidYr = @PaidYr GO The system variable @@rowcount will return the number of rows affected (in
this case inserted). Also, there is the variable @@error that contains <> 0 in the event of an error. For example: insert into ... if @@rowcount > 0 begin ... end Show quote "David Chase" <dlch***@lifetimeinc.com> wrote in message news:OdaU4pF8FHA.2616@TK2MSFTNGP15.phx.gbl... >I have a stored procedure (see below) that inserts records into a history >table from a source table (PayTotals). After that is successful, I would >like to run a delete statement "DELETE FROM dbo.PayTotals WHERE PaidYr = >@PaidYr" but only if the previous INSERT is successful. What would I need >to add to the proc below to make that happen? Thanks. > David > > CREATE PROCEDURE [mc_inshstPayTotals] > (@PaidYr [smallint]) > > AS INSERT INTO dbo.hstPayTotals > ([EmployerCaseNumber], > [EmployerMax], > [PayFirst], > [PaySecond], > [PaidYr], > [PaidMo]) > > SELECT > EmployerCaseNumber, > EmployerMax, > PayFirst, > PaySecond, > PaidYr, > PaidMo > FROM dbo.PayTotals > WHERE PaidYr = @PaidYr > GO > JT,
If there IS an error, won't the @@rowcount = 0? Thanks. *** Sent via Developersdex http://www.developersdex.com *** Yes, I would expect that to be the case.
Just because @@error = 0, it doesn't necessarily mean that 0 rows were inserted, becuase the select query may return no rows or a trigger on hstPayTotals may rollback the insert. Therefore, you will want to at least check the status of @@rowcount. Show quote "David" <da***@lifetime.com> wrote in message news:unZmE0F8FHA.1864@TK2MSFTNGP12.phx.gbl... > JT, > If there IS an error, won't the @@rowcount = 0? > Thanks. > > > > *** Sent via Developersdex http://www.developersdex.com *** What does "successful" mean? If it means exactly one row is inserted:
IF @@ROWCOUNT = 1 BEGIN DELETE ... END Otherwise, you'll have to be more specific... Show quote "David Chase" <dlch***@lifetimeinc.com> wrote in message news:OdaU4pF8FHA.2616@TK2MSFTNGP15.phx.gbl... >I have a stored procedure (see below) that inserts records into a history >table from a source table (PayTotals). After that is successful, I would >like to run a delete statement "DELETE FROM dbo.PayTotals WHERE PaidYr = >@PaidYr" but only if the previous INSERT is successful. What would I need >to add to the proc below to make that happen? Thanks. > David > > CREATE PROCEDURE [mc_inshstPayTotals] > (@PaidYr [smallint]) > > AS INSERT INTO dbo.hstPayTotals > ([EmployerCaseNumber], > [EmployerMax], > [PayFirst], > [PaySecond], > [PaidYr], > [PaidMo]) > > SELECT > EmployerCaseNumber, > EmployerMax, > PayFirst, > PaySecond, > PaidYr, > PaidMo > FROM dbo.PayTotals > WHERE PaidYr = @PaidYr > GO > > The INSERT will always handle thousands of records, so I would think
that if the @@rowcount was > 0 then it worked, correct? David *** Sent via Developersdex http://www.developersdex.com *** > The INSERT will always handle thousands of records, so I would think Well, again, just because you inserted a bunch of rows doesn't necessarily > that if the @@rowcount was > 0 then it worked, correct? mean they were the right ones. :-) But yes, if all you care about is that at least one row was inserted, then checking for a positive @@ROWCOUNT should suffice. >I have a stored procedure (see below) that inserts records into a history I must be missing something in the midst of all this discussion of >table from a source table (PayTotals). After that is successful, I would >like to run a delete statement "DELETE FROM dbo.PayTotals WHERE PaidYr = >@PaidYr" but only if the previous INSERT is successful. What would I need >to add to the proc below to make that happen? Thanks. @@ROWCOUNT and @@ERROR. Perhaps I'm being retarded today, but why not just do this: BEGIN TRANSACTION INSERT stuff DELETE stuff COMMIT TRANSACTION -- Peace & happy computing, Mike Labosh, MCSD "When you kill a man, you're a murderer. Kill many, and you're a conqueror. Kill them all and you're a god." -- Dave Mustane "Mike Labosh" <mlab***@hotmail.com> wrote in message It depends what you want to do if the INSERT fails.news:%23kJ4e7G8FHA.2616@TK2MSFTNGP15.phx.gbl... > I must be missing something in the midst of all this discussion of > @@ROWCOUNT and @@ERROR. Perhaps I'm being retarded today, but why not > just do this: > > BEGIN TRANSACTION > INSERT stuff > DELETE stuff > COMMIT TRANSACTION Do you still want to DELETE? And if the INSERT succedes and the DELETE fails. Do you still want to Commit the INSERT. Normally you don't. So after each one, check @@rowcount and @@errors and Commit only if both succede. Else, Rollback.  |
|||||||||||||||||||||||
Other interesting topics