global var in SQL

[aspijker]

Hi!

I am using a lot of transactions in a sp. Within each transaction I am updating a local variable, but as soon as a transaction is committed, SQL server makes my local variable empty, so what I need is some kind of global variable. But I can't declare my own gloval variable. A transaction looks like this:
declare @bericht varchar(255)
BEGIN TRANSACTION number1
-- some insers etc.
IF @@Error<>0
BEGIN
SELECT @bericht = @bericht + 'some more text' + char(13)
ROLLBACK TRANSACTION number1
RETURN
END
ELSE
BEGIN
SELECT @bericht = @bericht + 'some other text' + char(13)
COMMIT TRANSACTION number1
RETURN
END

print @bericht -- now after transaction is committed this value is emty again

Does anyone know how to solve this, I cannot make an extra table to store the variable.

Thanks, Salsa

 

[philhege]

Try initiating the variable with a space or empty string. If it's NULL to begin with, its value will be NULL even if you concatenate it with the other information.

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[djj55]

Hello,
Could you put it in a temporary table? #
djj

 

[aspijker]

I rather not use a temporary table

 

[SQLSister]

What is wrong with using a temp table? That would be the best method for waht you describe.

"NOTHING is more important in a database than integrity." ESquared

 

[aspijker]

Yes, I am using a temp table now, but only because there is no better way. For performance issues I rather not use them.

Thank you for all your replies!

Regards, Salsa