Select in transaction??

12 Nov 2005 3:05 AM

ReTF

Hi All,
I did read that I cant put SELECT in one transaction, but I need put one
SELECT in one transaction(see code), because I need have certainty that this
SELECT was executed, how I should do this?

BEGIN TRANSACTION
INSERT INTO transacoes(
data,
terminal,
documento,
autorizacao,
autenticacao,
lsn,
estabeleciento,
cartao,
financiamento,
parcelas,
total
)
VALUES(
@dt,
@terminal,
@dnum,
@auto,
@aute,
@lsn,
@estabeleciento,
@cartao,
@financiamento,
@parcelas,
@vl)

IF (@@ERROR <> 0)
BEGIN
   SET @ret = 1
   ROLLBACK TRANSACTION
   RETURN
END

SELECT SCOPE_IDENTITY(), nome_cupom, endereco_cupom, numero, @terminal,
@estabeleciento, @dt, @auto, @dnum, @aute, @vl FROM estabelecimentos WHERE
estabelecimento_id = @estabeleciento

IF (@@ERROR <> 0)
BEGIN
   SET @ret = 1
   ROLLBACK TRANSACTION
   RETURN
END

COMMIT TRANSACTION

12 Nov 2005 3:50 AM

Alejandro Mesa

I think you do not need a transaction here, You can try something like:

INSERT INTO transacoes(
data,
terminal,
documento,
autorizacao,
autenticacao,
lsn,
estabeleciento,
cartao,
financiamento,
parcelas,
total
)
VALUES(
@dt,
@terminal,
@dnum,
@auto,
@aute,
@lsn,
@estabeleciento,
@cartao,
@financiamento,
@parcelas,
@vl)

if @@error = 0
     begin
     select ...
     return 0
     end
else
     begin
     raiserror('...', 16, 1)
     return 1
     end
go

AMB

Show quote

"ReTF" wrote:

> Hi All,
> I did read that I cant put SELECT in one transaction, but I need put one
> SELECT in one transaction(see code), because I need have certainty that this
> SELECT was executed, how I should do this?
>
>
>   BEGIN TRANSACTION
>   INSERT INTO transacoes(
>   data,
>   terminal,
>   documento,
>   autorizacao,
>   autenticacao,
>   lsn,
>   estabeleciento,
>   cartao,
>   financiamento,
>   parcelas,
>   total
>   )
>   VALUES(
>   @dt,
>   @terminal,
>   @dnum,
>   @auto,
>   @aute,
>   @lsn,
>   @estabeleciento,
>   @cartao,
>   @financiamento,
>   @parcelas,
>   @vl)
>
>   IF (@@ERROR <> 0)
>   BEGIN
>    SET @ret = 1
>    ROLLBACK TRANSACTION
>    RETURN
>   END
>
>   SELECT SCOPE_IDENTITY(), nome_cupom, endereco_cupom, numero, @terminal,
> @estabeleciento, @dt, @auto, @dnum, @aute, @vl FROM estabelecimentos WHERE
> estabelecimento_id = @estabeleciento
>
>   IF (@@ERROR <> 0)
>   BEGIN
>    SET @ret = 1
>    ROLLBACK TRANSACTION
>    RETURN
>   END
>
> COMMIT TRANSACTION
>
>
>

12 Nov 2005 11:41 PM

Erland Sommarskog

ReTF (re.tf@newsgroup.nospam) writes:

> I did read that I cant put SELECT in one transaction, but I need put one
> SELECT in one transaction(see code), because I need have certainty that
> this SELECT was executed, how I should do this?

It's perfectly possible to have SELECT statements in a transaction,
and sometimes it may be necessary. This is probably not such a case
though, as all you have is a single INSERT.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

13 Nov 2005 5:26 PM

JXStern

It's good the way you did it!

Josh

Show quote

On Sat, 12 Nov 2005 01:05:56 -0200, "ReTF" <re.tf@newsgroup.nospam>
wrote:
>Hi All,
>I did read that I cant put SELECT in one transaction, but I need put one
>SELECT in one transaction(see code), because I need have certainty that this
>SELECT was executed, how I should do this?
>
>
> BEGIN TRANSACTION
> INSERT INTO transacoes(
> data,
> terminal,
> documento,
> autorizacao,
> autenticacao,
> lsn,
> estabeleciento,
> cartao,
> financiamento,
> parcelas,
> total
> )
> VALUES(
> @dt,
> @terminal,
> @dnum,
> @auto,
> @aute,
> @lsn,
> @estabeleciento,
> @cartao,
> @financiamento,
> @parcelas,
> @vl)
>
> IF (@@ERROR <> 0)
> BEGIN
>   SET @ret = 1
>   ROLLBACK TRANSACTION
>   RETURN
> END
>
> SELECT SCOPE_IDENTITY(), nome_cupom, endereco_cupom, numero, @terminal,
>@estabeleciento, @dt, @auto, @dnum, @aute, @vl FROM estabelecimentos WHERE
>estabelecimento_id = @estabeleciento
>
> IF (@@ERROR <> 0)
> BEGIN
>   SET @ret = 1
>   ROLLBACK TRANSACTION
>   RETURN
> END
>
> COMMIT TRANSACTION
>

14 Nov 2005 8:26 AM

Sophie Guo [MSFT]

Hello,

The following can be run successfully in QA.

begin tran
select * from pubs.dbo.authors
commit tran

So we can run select statement in transaction.

Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.