CODEWITHSUNDEEP
sql-server
Ivan-Mark Debono
Ivan-Mark Debono

Reputation: 16320

Is CAST necessary when writing non-varchar columns to a text column?

I have a trigger that takes the columns (and their values) from the inserted table and inserts them as text in an audit table, example:

INSERT INTO audit 
    (tablename, changes)
SELECT
    'mytable',
    'id=' + cast(id as nvarchar(50) + ';name=' + name + ';etc...'
FROM 
    inserted

I have large tables with most columns being non-varchar. In order to concatenate them into a string I need to cast each and every column.

Is it necessary to do so? Is there a better way?

The second (unmarked answer) in this question concatenates the values smartly using xml and cross apply.

Is there a way to expand it to include the column names so that the final result would be:

'id=1;name=myname;amount=100;' etc....

Upvotes: 0

Views: 116

Answers (2)

Luaan
Luaan

Reputation: 63772

Why not just save it as xml? 

select * from inserted for xml auto

It usually takes less space than a simple text column, and you can treat it as (relatively) normalized data. And most importantly, you don't have to handle converting all the complicated stuff manually (how does your code handle end-lines, quotes...?).

In fact, you can even add indices to xml columns, so it can even be practical to search through. Even without indices, it's going to be much faster searching e.g. all records changed in mytable that set name to some value.

And of course, you can keep the same piece of code for all your audited tables - no need to keep them in sync with the table structures etc. (unless you want to select only some explicit columns, of course).

Upvotes: 1

Rhys Jones
Rhys Jones

Reputation: 5518

Yes, you need to cast non-character data to a string before you can concatenate it. You might want to use CONVERT instead for data that is susceptible to regional formatting (I'm specifically thinking dates here) to ensure you get a deterministic result. You also need to handle nullable columns, i.e. ISNULL(CAST(MyColumn AS VARCHAR(100)), '') - if you concatenate a NULL it will NULL the whole string.

Upvotes: 1

Related Questions