Process a row with unknown structure in a cursor

问题:

I am new to using cursors for looping through a set of rows. But so far I had prior knowledge of which columns I am about to read.

E.g.

DECLARE db_cursor FOR 
SELECT Column1, Column2
FROM MyTable

DECLARE @ColumnOne VARCHAR(50), @ColumnTwo VARCHAR(50)

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @ColumnOne, @ColumnTwo
...

But the tables I am about to read into my key/value table have no specific structure and I should be able to process them one row at a time. How, using a nested cursor, can I loop through all the columns of the fetched row and process them according to their type and name?

回答1:

TSQL cursors are not really designed to read data from tables of unknown structure. The two possibilities I can think of to achieve something in that direction are:

• First read the column names of an unknown table from the Information Schema Views (see System Information Schema Views (Transact-SQL)). Then use dynamic SQL to create the cursor.
• If you simply want to get any columns as a large string value, you might also try a simple SELECT * FROM TABLE_NAME FOR XML AUTO and further process the retrieved data for your purposes (see FOR XML (SQL Server)).

回答2:

SQL is not very good in dealing with sets generically. In most cases you must know column names, data types and much more in advance. But there is XQuery. You can transform any SELECT into XML rather easily and use the mighty abilities to deal with generic structures there. I would not recommend this, but it might be worth a try:

CREATE PROCEDURE dbo.Get_EAV_FROM_SELECT
(
     @SELECT NVARCHAR(MAX)
)
AS
BEGIN
    DECLARE @tmptbl TABLE(TheContent XML);
    DECLARE @cmd NVARCHAR(MAX)= N'SELECT (' + @SELECT + N' FOR XML RAW, ELEMENTS XSINIL);';
    INSERT INTO @tmptbl EXEC(@cmd);

    SELECT r.value('*[1]/text()[1]','nvarchar(max)') AS RowID
          ,c.value('local-name(.)','nvarchar(max)') AS ColumnKey
          ,c.value('text()[1]','nvarchar(max)') AS ColumnValue
    FROM @tmptbl t
    CROSS APPLY t.TheContent.nodes('/row') A(r)
    CROSS APPLY A.r.nodes('*[position()>1]') B(c)
END;
GO

EXEC Get_EAV_FROM_SELECT @SELECT='SELECT TOP 10 o.object_id,o.* FROM sys.objects o';
GO
--Clean-Up for test purpose
DROP PROCEDURE Get_EAV_FROM_SELECT;

The idea in short

• The select is passed into the procedure as string. With the SP we create a statement dynamically and create XML from it.
• The very first column is considered to be the Row's ID, if not (like in sys.objects) we can write the SELECT and force it that way.
• The inner SELECT will read each row and return a classical EAV-list.