To smooth query results received by OUTPUT into plain text using T-SQL?

Question

I use the OUTPUT clause in my UPDATE and INSERT for the protocol of changes made to the database by my script. For logging, I would like to smooth the output of the OUTPUT clause into a single plaintext value that I could insert into one cell of my debug audit table.

The only solution I decided with was to manually concatenate all the output fields, but since some of them may be NULL , I also have to check this before concatenation, and the final script looks ugly.

Is there an easier and more elegant way to smooth out the output into plain text, similar to how it is done with the XML FOR XML clause?

Answer 1

Try this option -

 ALTER TRIGGER dbo.[trg_IOIU_test] ON dbo.[vw_test] INSTEAD OF INSERT, UPDATE AS BEGIN SET NOCOUNT ON SET XACT_ABORT ON DECLARE @DocumentUID UNIQUEIDENTIFIER, @Date DATETIME DECLARE cur CURSOR FORWARD_ONLY READ_ONLY FOR SELECT DocumentUID, [Date] FROM INSERTED OPEN cur FETCH NEXT FROM cur INTO @DocumentUID, @Date WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @BeforeChange NVARCHAR(MAX) , @AfterChange NVARCHAR(MAX) SET @BeforeChange = ( SELECT * FROM DELETED WHERE [DocumentUID] = @DocumentUID FOR XML RAW, ROOT ) SET @AfterChange = ( SELECT * FROM INSERTED WHERE [DocumentUID] = @DocumentUID FOR XML RAW, ROOT ) -- insert/update statement EXEC dbo.[AddLog] @DocumentUID = @DocumentUID, @BeforeChange = @BeforeChange, @AfterChange = @AfterChange FETCH NEXT FROM cur INTO @DocumentUID, @Date END CLOSE cur DEALLOCATE cur END