Trying to get output of PRINT command after select in Transaction

Question

Post reply

Trying to get output of PRINT command after select in Transaction

boughrogers

SSC Enthusiast

Points: 183
More actions
January 20, 2021 at 8:45 am

#3835397
Hi
I am looking to select the output of the data that I plan to update if rolling back the data, and print out some details as per the code below. When I run the select it doesn't then return the PRINT commands after just the output of the SQL. Is this all related to the buffer data, sure obvious but just trying to find the best way to display the data and output of the PRINT statement.
Thanks
Ian
```
 IF (@debug = 1) -- If Chosen to update, then commit changes
BEGIN
COMMIT TRANSACTION TRAN1; 
PRINT 'Site updated,Transaction committed'
END
ELSE -- If run in debug then rollback updates applied
BEGIN
select electrician,meteringactual,arrivalEnergyStatus,DepartureEnergyStatus from Appointments where id in (select appID from #CompleteTemp)
ROLLBACK TRANSACTION TRAN1; 
PRINT 'No site data updated. Transaction rolled back'
END
```

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

PRINT goes to the messages tab in SSMS as its information not data

If you want it in the data result pane or data set you would want to use SELECT instead of PRINT

 IF (@debug = 1) -- If Chosen to update, then commit changes
BEGIN
COMMIT TRANSACTION TRAN1; 
SELECT 'Site updated,Transaction committed'
END
ELSE -- If run in debug then rollback updates applied
BEGIN
select electrician,meteringactual,arrivalEnergyStatus,DepartureEnergyStatus from Appointments where id in (select appID from #CompleteTemp)
ROLLBACK TRANSACTION TRAN1; 
SELECT 'No site data updated. Transaction rolled back'
END

boughrogers SSC Enthusiast Points: 183 More actions · Answer 2

Hi Ant-Green

Yes sorry missed that, schoolboy my end.

Thanks for the reply

Regards

Ian

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

PRINT may also be substantially delayed an may not show up in some job output in SQL Agent. I use RAISERROR('somemessagehere',0,0) WITH NOWAIT; for almost real time feedback (but even that has a limit and will start "chunking" in groups of 100 messages after something like 500 messages have been displayed... yeah... I know... don't ask... :D).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ratbak Ten Centuries Points: 1328 More actions · Answer 4

"after something like 500 messages have been displayed... yeah... I know... don't ask..."

Ouch. I'd like to see that. Not on my servers, mind you, but it would be interesting to see such a beast.