T-SQL Output Clause

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by bitbucket » T-SQL Output Clause

37 posts, Page 1 of 4

1 2 3 4 »»»

T-SQL Output Clause

Rate Topic

Display Mode

Topic Options

Author

Message

bitbucket-25253

Posted Tuesday, January 05, 2010 9:41 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 5,101, Visits: 20,200

Comments posted to this topic are about the item T-SQL Output Clause

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read
Before posting a performance problem please read

Post #842538

Rich Holt

Posted Tuesday, January 05, 2010 11:51 PM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:44 AM
Points: 91, Visits: 94

If, for some reason, you wanted to keep track of work regardless of the transaction state, you could use a table variable instead.

Table variables exist outside the scope of the transaction, and therefore are not rolled back.

For example:

DECLARE @CategoryChanges TABLE
( ChangeID int Primary Key Identity
, CategoryID int
, OldCategoryName nvarchar(15)
, NewCategoryName nvarchar(15)
, ModifiedDate datetime2
, LoginID nvarchar(30));

BEGIN TRANSACTION
UPDATE Categories
SET CategoryName = 'Dried Produce'
OUTPUT inserted.CategoryID, deleted.CategoryName
, inserted.CategoryName, getdate(), SUSER_SNAME()
INTO @CategoryChanges
WHERE CategoryID = 7;
SELECT * FROM @CategoryChanges --first select statement
ROLLBACK TRANSACTION
SELECT * FROM @CategoryChanges --second select statement

Post #842559

ziangij

Posted Wednesday, January 06, 2010 12:09 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:53 AM
Points: 1,528, Visits: 359

thanks bitbucket...

i got to know one more difference between temp tables and table variable.

Post #842565

Rich Holt

Posted Wednesday, January 06, 2010 1:00 AM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:44 AM
Points: 91, Visits: 94

I should also caution you about the other way to look at this:

If you insert/update/delete TABLE variable rows inside a transaction, and subsequently rollback the work, the table variable changes are not rolled back with the transaction. This could lead to data inconsistencies.
Be careful.

Post #842579

Adi Cohn-120898

Posted Wednesday, January 06, 2010 1:24 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 2:19 AM
Points: 2,018, Visits: 4,915

I’m sorry, but this question is not a good one. With the information that we got from the question, there is no way that can know how many records will be returned by the first select statement. The only thing that we can know for sure is that the second select statement will not return any records. Since answer included 2 optional answers that said that the second select statement will return no records and the only difference between them was the number of records that will be returned by the first select statement, I think that both of them can be regarded as correct answer.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #842583

Andrew Watson-478275

Posted Wednesday, January 06, 2010 2:26 AM

SSC Eights!

Group: General Forum Members
Last Login: Saturday, May 18, 2013 1:44 AM
Points: 953, Visits: 1,875

I don't think that the problem is that we can't know which is correct as Adi says, but that to know which is correct, you have to have Northwind installed, which not everybody does.

Post #842606

VALEK

Posted Wednesday, January 06, 2010 2:52 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:45 PM
Points: 374, Visits: 422

The question is trying to test the knowledge of temp tables with TSQL transactions, and does it very badly.
There is not enough information.
You should not require any database installed on your computer, and in fact should not even try to run the statement to answer for it would be cheating.

The question sucks.
There is one answer missing, which is correct -- UNKNOWN.

M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM

Post #842624

Adi Cohn-120898

Posted Wednesday, January 06, 2010 4:26 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 2:19 AM
Points: 2,018, Visits: 4,915

VALEK (1/6/2010)

Try not to take the question of the day so personally. I agree that the question has some issues and I didn’t like it, but you don’t have to be so blunt in your message. I also disagree that the only answer that could be correct is unknown. For example let take the same question and give you the fallowing options:
1) First select statements returned 10 records. Second returned 0 records
2) First select statement returned 10 records. Second returned 10 records.
3) First select statement returned 0 records. Second returned 10 records.
I think that given those options, the correct answer can be the first one (notice that unknown was not in the options).
Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #842673

BudaCli

Posted Wednesday, January 06, 2010 5:16 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 9:02 AM
Points: 1,046, Visits: 573

Tx learned 1 or 2 things & also did it wif a table variable and saw different results as well.

DECLARE @CategoryChanges TABLE
(
ChangeID int Primary Key Identity ,
CategoryID int ,
OldCategoryName nvarchar(15),
NewCategoryName nvarchar(15),
ModifiedDate datetime,
LoginID nvarchar(30)
);

BEGIN TRAN
INSERT @CategoryChanges
SELECT 1, 'Cat1', 'Cat2', GETDATE(), SUSER_SNAME()
SELECT * FROM @CategoryChanges
ROLLBACK TRAN

SELECT * FROM @CategoryChanges

What you don't know won't hurt you but what you know will make you plan to know better

Post #842689

john.curran.z3g

Posted Wednesday, January 06, 2010 5:55 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, February 18, 2013 7:25 AM
Points: 236, Visits: 53

Ideally this question would have included the Categories table with the INSERT to remove the dependency on Northwind. This would also remove any doubt as to the number of records returned.

Post #842704

« Prev Topic | Next Topic »

37 posts, Page 1 of 4

1 2 3 4 »»»

Permissions