将来自不同表的行连接到一个字段中

In a project using a MSSQL 2005 Database we are required to log all data manipulating actions in a logging table. One field in that table is supposed to contain the row before it was changed. We have a lot of tables so I was trying to write a stored procedure that would gather up all the fields in one row of a table that was given to it, concatenate them somehow and then write a new log entry with that information.

在使用MSSQL 2005数据库的项目中,我们需要在日志记录表中记录所有数据操作操作。该表中的一个字段应该在更改之前包含该行。我们有很多表,所以我试图编写一个存储过程,它将收集给它的一行表中的所有字段,以某种方式连接它们,然后用该信息写一个新的日志条目。

I already tried using FOR XML PATH and it worked, but the client doesn't like the XML notation, they want a csv field.

我已经尝试使用FOR XML PATH并且它有效,但是客户端不喜欢XML表示法,他们想要一个csv字段。

Here's what I had with FOR XML PATH:

以下是我对FOR XML PATH的看法:

DECLARE @foo varchar(max);
SET @foo = (SELECT * FROM table WHERE id = 5775 FOR XML PATH(''));

The values for "table", "id" and the actual id (here: 5775) would later be passed in via the call to the stored procedure.

“table”,“id”和实际id(此处为:5775)的值稍后将通过调用传递给存储过程。

Is there any way to do this without getting XML notation and without knowing in advance which fields are going to be returned by the SELECT statement?

如果没有获得XML表示法并且事先不知道SELECT语句将返回哪些字段,有没有办法做到这一点?

2 个解决方案

#1

We used XML path and as you've discovered, it works very well. Since one of SQL's features is to store XML properly, the CSV makes no sense.

我们使用XML路径,正如您所发现的,它运行良好。由于SQL的一个特性是正确存储XML,因此CSV毫无意义。

What you could try is a stored proc that reads out the XML in CSV format (fake it). I would. Since you won't likely be reading the data that much compared to saving it, the overhead is negligible.

你可以尝试的是一个存储过程,它以CSV格式读出XML(伪造它)。我会。由于与保存数据相比,您不太可能读取数据,因此开销可以忽略不计。

#2

How about:

Set @Foo = Stuff(
                        ( Select ',' + MyCol1 + ',' + MyCol2 ...
                            From Table
                            Where Id = 5775 
                            For Xml Path('') 
                        ), 1, 1, '')

This will produce a CSV line (presuming the inner SQL returns a single row). Now, this solves the second part of your question. As to the first part of "without knowing in advance which fields", there is no means to do this without using dynamic SQL. I.e., you have to build the SQL statement as a string on the fly. If you are going to do that you might as well build the entire CSV result on the fly.

这将产生一个CSV行(假设内部SQL返回一行)。现在,这解决了问题的第二部分。至于“事先不知道哪个字段”的第一部分,没有使用动态SQL就没有办法做到这一点。即,您必须动态地将SQL语句构建为字符串。如果您打算这样做,您也可以动态构建整个CSV结果。

#1