newbie question about transactions

I'm thinking of using transactions but there's something I don't know.
Consider that kind of code:
BEGIN TRANS
INSERT ...
INSERT ...
UPDATE ...
DELETE ...
INSERT...
COMMIT
My question is:
do I have to write after *each* insert, update or delete
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
RETURN
END
for my procedure to work well?
It's not a big deal if there are only 2 or 3 operations, but if there are
lots of them...
Can you tell me what the minimum code is for a procedure using transaction
to be valid?
Thanks
Henri
Hi
You have to check after each statement as the @.@.error variable gets reset
every time. In your example, you need the error handler 7 times, once after
each statement (you could get away with 6, excluding the BEGIN TRAN as you
should not error on that).
SQL Server 2005 brings structured exception handling, but until then, that
is the only way.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Henri" <hmfireball@.hotmail.com> wrote in message
news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
> I'm thinking of using transactions but there's something I don't know.
> Consider that kind of code:
> BEGIN TRANS
> INSERT ...
> INSERT ...
> UPDATE ...
> DELETE ...
> INSERT...
> COMMIT
> My question is:
> do I have to write after *each* insert, update or delete
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> RETURN
> END
> for my procedure to work well?
> It's not a big deal if there are only 2 or 3 operations, but if there are
> lots of them...
> Can you tell me what the minimum code is for a procedure using transaction
> to be valid?
> Thanks
> Henri
>
>
|||Thanks a lot for your answer Mike :-)
Henri
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a crit dans le message de
news:eIDrTa20EHA.1932@.TK2MSFTNGP09.phx.gbl...
> Hi
> You have to check after each statement as the @.@.error variable gets reset
> every time. In your example, you need the error handler 7 times, once
after[vbcol=seagreen]
> each statement (you could get away with 6, excluding the BEGIN TRAN as you
> should not error on that).
> SQL Server 2005 brings structured exception handling, but until then, that
> is the only way.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Henri" <hmfireball@.hotmail.com> wrote in message
> news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
are[vbcol=seagreen]
transaction
>
>
|||I like to defer my exception handling and sometimes have inline conditions. The following is a general layout that I use, but the one you describe is typical as well.
CREATE PROCEDURE DataModificationTransaction1
@.Param1 AS DataType1
,@.Param2 AS DataType2
...
,@.ParamN AS DataTypeN
AS
/*
**
** Procedure Information Comment Block
**
*/
DECLARE @.intTranCountOnEntry AS INT
,@.intErrorCode AS INT
-- Environment Configuration.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Variable Initialization.
SET @.intErrorCode = @.@.ERROR
IF @.intErrorCode = 0 BEGIN
-- Capture transaction state before beginning.
SET @.intTranCountOnEntry = @.@.TRANCOUNT
BEGIN TRANSACTION
SET @.intErrorCode = @.@.ERROR
END
-- Only continue if error free.
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
UPDATE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
DELETE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
-- Only commit if transaction initiated
-- and error free.
IF @.@.TRANCOUNT > @.intTranCountOnEntry BEGIN
IF @.intErrorCode = 0 BEGIN
COMMIT TRANSACTION
END
ELSE BEGIN
ROLLBACK TRANSACTION
END
END
RETURN @.intErrorCode
You can also nest the conditional statements but you MUST check the status of @.@.ERROR after each DML statement if you wish to properly trap errors. Also, it is VERY important that you initialize the appropriate environmental parameters on code launch since transactions are highly sensitive to these settings. Being explicit will help you in any debugging situations.
Hope this helps.
Sincerely,
Anthony Thomas

"Henri" <hmfireball@.hotmail.com> wrote in message news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
I'm thinking of using transactions but there's something I don't know.
Consider that kind of code:
BEGIN TRANS
INSERT ...
INSERT ...
UPDATE ...
DELETE ...
INSERT...
COMMIT
My question is:
do I have to write after *each* insert, update or delete
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
RETURN
END
for my procedure to work well?
It's not a big deal if there are only 2 or 3 operations, but if there are
lots of them...
Can you tell me what the minimum code is for a procedure using transaction
to be valid?
Thanks
Henri
|||Thanks for your help Anthony :-)
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> a crit dans le message de news:O7n%23T9K1EHA.1076@.TK2MSFTNGP09.phx.gbl...
I like to defer my exception handling and sometimes have inline conditions. The following is a general layout that I use, but the one you describe is typical as well.
CREATE PROCEDURE DataModificationTransaction1
@.Param1 AS DataType1
,@.Param2 AS DataType2
...
,@.ParamN AS DataTypeN
AS
/*
**
** Procedure Information Comment Block
**
*/
DECLARE @.intTranCountOnEntry AS INT
,@.intErrorCode AS INT
-- Environment Configuration.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Variable Initialization.
SET @.intErrorCode = @.@.ERROR
IF @.intErrorCode = 0 BEGIN
-- Capture transaction state before beginning.
SET @.intTranCountOnEntry = @.@.TRANCOUNT
BEGIN TRANSACTION
SET @.intErrorCode = @.@.ERROR
END
-- Only continue if error free.
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
UPDATE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
DELETE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
-- Only commit if transaction initiated
-- and error free.
IF @.@.TRANCOUNT > @.intTranCountOnEntry BEGIN
IF @.intErrorCode = 0 BEGIN
COMMIT TRANSACTION
END
ELSE BEGIN
ROLLBACK TRANSACTION
END
END
RETURN @.intErrorCode
You can also nest the conditional statements but you MUST check the status of @.@.ERROR after each DML statement if you wish to properly trap errors. Also, it is VERY important that you initialize the appropriate environmental parameters on code launch since transactions are highly sensitive to these settings. Being explicit will help you in any debugging situations.
Hope this helps.
Sincerely,
Anthony Thomas

"Henri" <hmfireball@.hotmail.com> wrote in message news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
I'm thinking of using transactions but there's something I don't know.
Consider that kind of code:
BEGIN TRANS
INSERT ...
INSERT ...
UPDATE ...
DELETE ...
INSERT...
COMMIT
My question is:
do I have to write after *each* insert, update or delete
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
RETURN
END
for my procedure to work well?
It's not a big deal if there are only 2 or 3 operations, but if there are
lots of them...
Can you tell me what the minimum code is for a procedure using transaction
to be valid?
Thanks
Henri

newbie question about transactions

I'm thinking of using transactions but there's something I don't know.
Consider that kind of code:
BEGIN TRANS
INSERT ...
INSERT ...
UPDATE ...
DELETE ...
INSERT...
COMMIT
My question is:
do I have to write after *each* insert, update or delete
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
RETURN
END
for my procedure to work well?
It's not a big deal if there are only 2 or 3 operations, but if there are
lots of them...
Can you tell me what the minimum code is for a procedure using transaction
to be valid?
Thanks
HenriHi
You have to check after each statement as the @.@.error variable gets reset
every time. In your example, you need the error handler 7 times, once after
each statement (you could get away with 6, excluding the BEGIN TRAN as you
should not error on that).
SQL Server 2005 brings structured exception handling, but until then, that
is the only way.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Henri" <hmfireball@.hotmail.com> wrote in message
news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
> I'm thinking of using transactions but there's something I don't know.
> Consider that kind of code:
> BEGIN TRANS
> INSERT ...
> INSERT ...
> UPDATE ...
> DELETE ...
> INSERT...
> COMMIT
> My question is:
> do I have to write after *each* insert, update or delete
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> RETURN
> END
> for my procedure to work well?
> It's not a big deal if there are only 2 or 3 operations, but if there are
> lots of them...
> Can you tell me what the minimum code is for a procedure using transaction
> to be valid?
> Thanks
> Henri
>
>|||Thanks a lot for your answer Mike :-)
Henri
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a crit dans le message de
news:eIDrTa20EHA.1932@.TK2MSFTNGP09.phx.gbl...
> Hi
> You have to check after each statement as the @.@.error variable gets reset
> every time. In your example, you need the error handler 7 times, once
after
> each statement (you could get away with 6, excluding the BEGIN TRAN as you
> should not error on that).
> SQL Server 2005 brings structured exception handling, but until then, that
> is the only way.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Henri" <hmfireball@.hotmail.com> wrote in message
> news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
are[vbcol=seagreen]
transaction[vbcol=seagreen]
>
>|||I like to defer my exception handling and sometimes have inline conditions.
The following is a general layout that I use, but the one you describe is t
ypical as well.
CREATE PROCEDURE DataModificationTransaction1
@.Param1 AS DataType1
,@.Param2 AS DataType2
...
,@.ParamN AS DataTypeN
AS
/*
**
** Procedure Information Comment Block
**
*/
DECLARE @.intTranCountOnEntry AS INT
,@.intErrorCode AS INT
-- Environment Configuration.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Variable Initialization.
SET @.intErrorCode = @.@.ERROR
IF @.intErrorCode = 0 BEGIN
-- Capture transaction state before beginning.
SET @.intTranCountOnEntry = @.@.TRANCOUNT
BEGIN TRANSACTION
SET @.intErrorCode = @.@.ERROR
END
-- Only continue if error free.
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
UPDATE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
DELETE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
-- Only commit if transaction initiated
-- and error free.
IF @.@.TRANCOUNT > @.intTranCountOnEntry BEGIN
IF @.intErrorCode = 0 BEGIN
COMMIT TRANSACTION
END
ELSE BEGIN
ROLLBACK TRANSACTION
END
END
RETURN @.intErrorCode
You can also nest the conditional statements but you MUST check the status o
f @.@.ERROR after each DML statement if you wish to properly trap errors. Als
o, it is VERY important that you initialize the appropriate environmental pa
rameters on code launch since transactions are highly sensitive to these set
tings. Being explicit will help you in any debugging situations.
Hope this helps.
Sincerely,
Anthony Thomas
--
"Henri" <hmfireball@.hotmail.com> wrote in message news:eC9loV10EHA.3484@.TK
2MSFTNGP09.phx.gbl...
I'm thinking of using transactions but there's something I don't know.
Consider that kind of code:
BEGIN TRANS
INSERT ...
INSERT ...
UPDATE ...
DELETE ...
INSERT...
COMMIT
My question is:
do I have to write after *each* insert, update or delete
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
RETURN
END
for my procedure to work well?
It's not a big deal if there are only 2 or 3 operations, but if there are
lots of them...
Can you tell me what the minimum code is for a procedure using transaction
to be valid?
Thanks
Henri|||Thanks for your help Anthony :-)
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> a crit dans le message de
news:O7n%23T9K1EHA.1076@.TK2MSFTNGP09.phx.gbl...
I like to defer my exception handling and sometimes have inline conditions.
The following is a general layout that I use, but the one you describe is t
ypical as well.
CREATE PROCEDURE DataModificationTransaction1
@.Param1 AS DataType1
,@.Param2 AS DataType2
..
,@.ParamN AS DataTypeN
AS
/*
**
** Procedure Information Comment Block
**
*/
DECLARE @.intTranCountOnEntry AS INT
,@.intErrorCode AS INT
-- Environment Configuration.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Variable Initialization.
SET @.intErrorCode = @.@.ERROR
IF @.intErrorCode = 0 BEGIN
-- Capture transaction state before beginning.
SET @.intTranCountOnEntry = @.@.TRANCOUNT
BEGIN TRANSACTION
SET @.intErrorCode = @.@.ERROR
END
-- Only continue if error free.
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
UPDATE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
DELETE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
-- Only commit if transaction initiated
-- and error free.
IF @.@.TRANCOUNT > @.intTranCountOnEntry BEGIN
IF @.intErrorCode = 0 BEGIN
COMMIT TRANSACTION
END
ELSE BEGIN
ROLLBACK TRANSACTION
END
END
RETURN @.intErrorCode
You can also nest the conditional statements but you MUST check the status o
f @.@.ERROR after each DML statement if you wish to properly trap errors. Als
o, it is VERY important that you initialize the appropriate environmental pa
rameters on code launch since transactions are highly sensitive to these set
tings. Being explicit will help you in any debugging situations.
Hope this helps.
Sincerely,
Anthony Thomas
--
"Henri" <hmfireball@.hotmail.com> wrote in message news:eC9loV10EHA.3484@.TK2M
SFTNGP09.phx.gbl...
I'm thinking of using transactions but there's something I don't know.
Consider that kind of code:
BEGIN TRANS
INSERT ...
INSERT ...
UPDATE ...
DELETE ...
INSERT...
COMMIT
My question is:
do I have to write after *each* insert, update or delete
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
RETURN
END
for my procedure to work well?
It's not a big deal if there are only 2 or 3 operations, but if there are
lots of them...
Can you tell me what the minimum code is for a procedure using transaction
to be valid?
Thanks
Henrisql

newbie question about transactions

I'm thinking of using transactions but there's something I don't know.
Consider that kind of code:
BEGIN TRANS
INSERT ...
INSERT ...
UPDATE ...
DELETE ...
INSERT...
COMMIT
My question is:
do I have to write after *each* insert, update or delete
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
RETURN
END
for my procedure to work well?
It's not a big deal if there are only 2 or 3 operations, but if there are
lots of them...
Can you tell me what the minimum code is for a procedure using transaction
to be valid?
Thanks
HenriHi
You have to check after each statement as the @.@.error variable gets reset
every time. In your example, you need the error handler 7 times, once after
each statement (you could get away with 6, excluding the BEGIN TRAN as you
should not error on that).
SQL Server 2005 brings structured exception handling, but until then, that
is the only way.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Henri" <hmfireball@.hotmail.com> wrote in message
news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
> I'm thinking of using transactions but there's something I don't know.
> Consider that kind of code:
> BEGIN TRANS
> INSERT ...
> INSERT ...
> UPDATE ...
> DELETE ...
> INSERT...
> COMMIT
> My question is:
> do I have to write after *each* insert, update or delete
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> RETURN
> END
> for my procedure to work well?
> It's not a big deal if there are only 2 or 3 operations, but if there are
> lots of them...
> Can you tell me what the minimum code is for a procedure using transaction
> to be valid?
> Thanks
> Henri
>
>|||Thanks a lot for your answer Mike :-)
Henri
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a écrit dans le message de
news:eIDrTa20EHA.1932@.TK2MSFTNGP09.phx.gbl...
> Hi
> You have to check after each statement as the @.@.error variable gets reset
> every time. In your example, you need the error handler 7 times, once
after
> each statement (you could get away with 6, excluding the BEGIN TRAN as you
> should not error on that).
> SQL Server 2005 brings structured exception handling, but until then, that
> is the only way.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Henri" <hmfireball@.hotmail.com> wrote in message
> news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
> > I'm thinking of using transactions but there's something I don't know.
> > Consider that kind of code:
> >
> > BEGIN TRANS
> >
> > INSERT ...
> > INSERT ...
> > UPDATE ...
> > DELETE ...
> > INSERT...
> >
> > COMMIT
> >
> > My question is:
> > do I have to write after *each* insert, update or delete
> > IF @.@.ERROR <> 0 BEGIN
> > ROLLBACK
> > RETURN
> > END
> >
> > for my procedure to work well?
> >
> > It's not a big deal if there are only 2 or 3 operations, but if there
are
> > lots of them...
> > Can you tell me what the minimum code is for a procedure using
transaction
> > to be valid?
> > Thanks
> >
> > Henri
> >
> >
> >
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0015_01C4D4EB.600E75E0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks for your help Anthony :-)
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> a =E9crit dans le =message de news:O7n%23T9K1EHA.1076@.TK2MSFTNGP09.phx.gbl...
I like to defer my exception handling and sometimes have inline =conditions. The following is a general layout that I use, but the one =you describe is typical as well.
CREATE PROCEDURE DataModificationTransaction1
@.Param1 AS DataType1
,@.Param2 AS DataType2
...
,@.ParamN AS DataTypeN
AS
/*
**
** Procedure Information Comment Block
** */
DECLARE @.intTranCountOnEntry AS INT
,@.intErrorCode AS INT
-- Environment Configuration.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Variable Initialization.
SET @.intErrorCode =3D @.@.ERROR
IF @.intErrorCode =3D 0 BEGIN
-- Capture transaction state before beginning.
SET @.intTranCountOnEntry =3D @.@.TRANCOUNT
BEGIN TRANSACTION
SET @.intErrorCode =3D @.@.ERROR
END
-- Only continue if error free.
IF @.intErrorCode =3D 0 BEGIN
INSERT ...
SET @.intErrorCode =3D @.@.ERROR
END
IF @.intErrorCode =3D 0 BEGIN
INSERT ...
SET @.intErrorCode =3D @.@.ERROR
END
IF @.intErrorCode =3D 0 BEGIN
UPDATE ...
SET @.intErrorCode =3D @.@.ERROR
END
IF @.intErrorCode =3D 0 BEGIN
DELETE ...
SET @.intErrorCode =3D @.@.ERROR
END
IF @.intErrorCode =3D 0 BEGIN
INSERT ...
SET @.intErrorCode =3D @.@.ERROR
END
-- Only commit if transaction initiated
-- and error free.
IF @.@.TRANCOUNT > @.intTranCountOnEntry BEGIN
IF @.intErrorCode =3D 0 BEGIN
COMMIT TRANSACTION
END
ELSE BEGIN
ROLLBACK TRANSACTION
END
END
RETURN @.intErrorCode
You can also nest the conditional statements but you MUST check the =status of @.@.ERROR after each DML statement if you wish to properly trap =errors. Also, it is VERY important that you initialize the appropriate =environmental parameters on code launch since transactions are highly =sensitive to these settings. Being explicit will help you in any =debugging situations.
Hope this helps.
Sincerely,
Anthony Thomas
-- "Henri" <hmfireball@.hotmail.com> wrote in message =news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
I'm thinking of using transactions but there's something I don't =know.
Consider that kind of code:
BEGIN TRANS
INSERT ...
INSERT ...
UPDATE ...
DELETE ...
INSERT...
COMMIT
My question is:
do I have to write after *each* insert, update or delete
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
RETURN
END
for my procedure to work well?
It's not a big deal if there are only 2 or 3 operations, but if =there are
lots of them...
Can you tell me what the minimum code is for a procedure using =transaction
to be valid?
Thanks
Henri
--=_NextPart_000_0015_01C4D4EB.600E75E0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Thanks for your help Anthony =:-)
"AnthonyThomas" a =E9crit dans le message de news:O7n%23T9K1EHA.=1076@.TK2MSFTNGP09.phx.gbl...
I like =to defer my exception handling and sometimes have inline conditions. The =following is a general layout that I use, but the one you describe is typical as = well.

CREATE =PROCEDURE DataModificationTransaction1 @.Param1 AS =DataType1 ,@.Param2 AS DataType2 ... ,@.ParamN AS DataTypeN AS

/***** =Procedure Information Comment Block** */

DECLARE @.intTranCountOnEntry AS INT ,@.intErrorCode AS =INT -- Environment Configuration.SET XACT_ABORT OFFSET =IMPLICIT_TRANSACTIONS OFFSET NOCOUNT ONSET TRANSACTION ISOLATION LEVEL SERIALIZABLE

-- Variable Initialization.SET @.intErrorCode =3D @.@.ERROR

IF =@.intErrorCode =3D 0 BEGIN -- Capture transaction state before =beginning. SET @.intTranCountOnEntry =3D @.@.TRANCOUNT BEGIN =TRANSACTION SET @.intErrorCode =3D @.@.ERROR END -- Only continue =if error free.IF @.intErrorCode =3D 0 BEGIN INSERT ... SET = @.intErrorCode =3D @.@.ERROR END IF @.intErrorCode ==3D 0 BEGIN INSERT ... SET @.intErrorCode =3D @.@.ERROR END IF @.intErrorCode =3D 0 =BEGIN UPDATE ... SET @.intErrorCode =3D =@.@.ERROR END IF @.intErrorCode =3D 0 BEGIN DELETE ... SET =@.intErrorCode =3D @.@.ERROR END IF @.intErrorCode =3D 0 =BEGIN INSERT ... SET @.intErrorCode =3D =@.@.ERROR END -- Only commit if transaction initiated-- and error free.IF =@.@.TRANCOUNT > @.intTranCountOnEntry BEGIN IF @.intErrorCode =3D 0 BEGIN COMMIT TRANSACTION END ELSE BEGIN ROLLBACK =TRANSACTION END END =RETURN @.intErrorCode
You can =also nest the conditional statements but you MUST check the status of @.@.ERROR after =each DML statement if you wish to properly trap errors. Also, it is VERY important that you initialize the appropriate environmental parameters =on code launch since transactions are highly sensitive to these =settings. Being explicit will help you in any debugging situations.

Hope =this helps.

Sincerely,


Anthony = Thomas



--
"Henri" =wrote in message news:eC9loV10EHA.3484=@.TK2MSFTNGP09.phx.gbl...I'm thinking of using transactions but there's something I don't know.Consider that kind of code:BEGIN =TRANSINSERT ...INSERT ...UPDATE ...DELETE ...INSERT...COMMITMy question is:do I have =to write after *each* insert, update or deleteIF @.@.ERROR 0 = BEGIN ROLLBACK RETURNENDfor my =procedure to work well?It's not a big deal if there are only 2 or 3 =operations, but if there arelots of them...Can you tell me what the =minimum code is for a procedure using transactionto be =valid?ThanksHenri

---

--=_NextPart_000_0015_01C4D4EB.600E75E0--

Newbie : Hard sql query?

Hey,
I have a table that records all my bank transactions. So in it there
is one entry per transaction. This entry has the date the transaction
occured.
All I want to do is perform a query that returns how many transactions
I had for each month.
Ie.
jan : 24 transactions
feb : 19 transactions
etc
is this possible? I know I can do "counts" ... but it seems hard
because I need to split the results return by the month it occurs in.
Any help you can give would be greatly appreciated!!
Thanx
Ryan RittenNot the most efficient way, but...
SELECT YEAR(TransactionDate), MONTH(TransactionDate), COUNT(*)
FROM TransactionTable
-- if you want to limit to specific year:
-- WHERE TransactionDate >= '20050101' AND TransactionDate < '20060101'
GROUP BY YEAR(TransactionDate), MONTH(TransactionDate)
ORDER BY 1,2
"Sparticus" <sparticusREMOVE@.thesparticusarena.com> wrote in message
news:1129914248.191858.104870@.f14g2000cwb.googlegroups.com...
> Hey,
> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
> Ie.
> jan : 24 transactions
> feb : 19 transactions
> etc
> is this possible? I know I can do "counts" ... but it seems hard
> because I need to split the results return by the month it occurs in.
> Any help you can give would be greatly appreciated!!
> Thanx
> Ryan Ritten
>|||> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
>
select Year(Date) As Year ,month(Date) Month, count(*) as TransCount from
YourTable
group by Year(Date),month(Date)
Francesco Anti|||Sparticus,
This should give you what you want. You'll need to WHERE for a specify year
or year range. If you want month names and/or you want the months with 0 or
as columns instead of rows...let me know.
SELECT DATEPART(MM,TDATE)AS 'MONTH', COUNT(*) 'TRANSACTION COUNT'
FROM #TRANSACTIONS
GROUP BY DATEPART(MM,TDATE)
HTH
Jerry
"Sparticus" <sparticusREMOVE@.thesparticusarena.com> wrote in message
news:1129914248.191858.104870@.f14g2000cwb.googlegroups.com...
> Hey,
> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
> Ie.
> jan : 24 transactions
> feb : 19 transactions
> etc
> is this possible? I know I can do "counts" ... but it seems hard
> because I need to split the results return by the month it occurs in.
> Any help you can give would be greatly appreciated!!
> Thanx
> Ryan Ritten
>|||wow...thanx everyone. Learn somethign new everyday :)|||group your data by datepart(month,datefield),datepart(year,
datefield)
http://sqlservercode.blogspot.com/
"Sparticus" wrote:

> Hey,
> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
> Ie.
> jan : 24 transactions
> feb : 19 transactions
> etc
> is this possible? I know I can do "counts" ... but it seems hard
> because I need to split the results return by the month it occurs in.
> Any help you can give would be greatly appreciated!!
> Thanx
> Ryan Ritten
>|||CREATE TABLE #MyTransaction
(
id int NOT NULL IDENTITY (1, 1),
[date] datetime NULL,
amount money NULL
) ON [PRIMARY]
insert into #MyTransaction
([date], amount)
values
('1-1-05', 100)
insert into #MyTransaction
([date], amount)
values
('1-10-05', 100)
insert into #MyTransaction
([date], amount)
values
('1-20-05', 100)
insert into #MyTransaction
([date], amount)
values
('2-1-05', 100)
insert into #MyTransaction
([date], amount)
values
('2-5-05', 100)
insert into #MyTransaction
([date], amount)
values
('2-7-05', 100)
insert into #MyTransaction
([date], amount)
values
('3-8-05', 10)
select datepart(yy,[date]) as Year, datename(mm,[date]) as month, count(id)
as Count
from #MyTransaction
group by datepart(yy,[date]), datename(mm,[date])
drop table #MyTransaction
"Sparticus" <sparticusREMOVE@.thesparticusarena.com> wrote in message
news:1129914248.191858.104870@.f14g2000cwb.googlegroups.com...
> Hey,
> I have a table that records all my bank transactions. So in it there
> is one entry per transaction. This entry has the date the transaction
> occured.
> All I want to do is perform a query that returns how many transactions
> I had for each month.
> Ie.
> jan : 24 transactions
> feb : 19 transactions
> etc
> is this possible? I know I can do "counts" ... but it seems hard
> because I need to split the results return by the month it occurs in.
> Any help you can give would be greatly appreciated!!
> Thanx
> Ryan Ritten
>