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
GOWhat 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...
"David Chase" <dlchase@.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 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
"David Chase" <dlchase@.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.examnotes.net ***|||JT,
If there IS an error, won't the @.@.rowcount = 0?
Thanks.
*** Sent via Developersdex http://www.examnotes.net ***|||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.
"David" <daman@.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.examnotes.net ***|||> The INSERT will always handle thousands of records, so I would think
> that if the @.@.rowcount was > 0 then it worked, correct?
Well, again, just because you inserted a bunch of rows doesn't necessarily
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
>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.
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
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" <mlabosh@.hotmail.com> wrote in message
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
It depends what you want to do if the INSERT fails.
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.
