主页 / dba / 问题 / 21046
Accepted
Taryn
Asked: 2012-07-18 14:08:02 +0800 CST

存储过程返回动态创建的表数据

  • 772

简短的背景故事，我们正在与拥有调查系统的外部供应商合作。当您创建新调查并且系统创建新表时，系统不一定设计得最好，即：

Tables
____
Library_1 -- table for Survey 1
SurveyId int
InstanceId int
Q_1 varchar(50)

Library_2 -- table for Survey 2
SurveyId int
InstanceId int
Q_2 int
Q_3 int
Q_4 varchar(255)

生成的表格SurveyId名称末尾带有 ( Library_)，而生成的问题列QuestionId末尾带有 ( Q_)。 为了澄清，问题存储在一个单独的表中，因此虽然问题 ID 是连续的，但它们不会从每个调查的 1 开始。问题列将基于表中分配给它们的 id。

查询起来似乎很简单，除了我们需要从所有调查表中提取数据以发送到另一个系统，这就是问题所在。由于表是在前台添加新调查时自动创建的 -最终应用程序，其他系统无法处理这种类型的结构。他们需要数据保持一致才能使用。

因此，我的任务是编写一个存储过程，该过程将从所有调查表中提取数据并将其放置在以下格式中：

SurveyId    InstanceId    QNumber    Response
________    __________    _______    ________
1           1             1          great
1           2             1          the best
2           9             2          10
3           50            50         test

通过使所有表格的数据采用相同的格式，任何人都可以使用它，无论存在多少调查表格和问题。

我编写了一个似乎正在工作的存储过程，但我想知道我是否遗漏了什么，或者是否有更好的方法来处理这种情况。

我的代码：

declare @sql varchar(max) = ''
declare @RowCount int = 1
declare @TotalRecords int = (SELECT COUNT(*) FROM SurveyData)

Declare @TableName varchar(50) = ''
Declare @ColumnName varchar(50) = ''

WHILE @RowCount <= @TotalRecords
    BEGIN

        SELECT @TableName = tableName, @ColumnName = columnName
        FROM SurveyData
        WHERE @RowCount = rownum


        SET @sql = @sql + 
            ' SELECT s.SurveyId
                , s.InstanceId
                , CASE WHEN columnName = ''' +  @ColumnName + ''' THEN REPLACE(columnName, ''Q_'', '''') ELSE '''' END as QuestionNumber
                , Cast(s.' + @ColumnName + ' as varchar(1000)) as ''Response''
            FROM SurveyData t 
            INNER JOIN ' + @TableName + ' s' +
                ' ON REPLACE(t.tableName, ''Library_'', '''') = s.SurveyID ' +
            ' WHERE t.columnName = ''' + @ColumnName + ''''

        IF @RowCount != @TotalRecords
            BEGIN
                set @sql = @sql + ' UNION ALL'
            END

        SET @RowCount = @RowCount + 1       
    END


exec(@sql)

我用一些示例数据和代码 创建了一个SQL Fiddle 。

这种类型的查询应该以不同的方式编写吗？它有什么明显的问题吗？

不幸的是，这有很多未知数......我们将拥有多少张表格以及每次调查有多少问题。 我想说我们将进行 25-50 次调查，每个调查有 2-5 个问题。

sql-server sql-server-2008-r2

1 个回答

  1. Best Answer

    根据聊天中人们的评论，我决定将我的脚本稍微更改为INSERT INTO一个临时表，而不是创建一条长 SQL 语句以在最后执行。所以最后我的存储过程包含以下内容：

    create table #SurveyData
(
    tableName varchar(50),
    columnName varchar(50),
    columnId int,
    rownum int
)

create table #results
(
    SurveyId int,
    InstanceId int,
    QuestionNumber int,
    Response varchar(1000)
)

-- insert the survey table structures for use
insert into #SurveyData (tableName, columnName, columnId, rownum)
select tables1.name, cols1.name, column_id, ROW_NUMBER() over(order by tables1.name, column_id)
from sys.all_columns cols1
inner join 
(
    SELECT *
    FROM sys.all_objects
    WHERE type = 'U' 
    AND upper(name) like 'LIBRARY%' 
) Tables1
    ON cols1.object_id = tables1.object_id
WHERE cols1.name Like 'Q_%'
ORDER BY tables1.name, column_id;


declare @sql varchar(max) = '';
declare @RowCount int = 1;
declare @TotalRecords int = (SELECT COUNT(*) FROM #SurveyData);

Declare @TableName varchar(50) = '';
Declare @ColumnName varchar(50) = '';

WHILE @RowCount <= @TotalRecords
    BEGIN

        SELECT @TableName = tableName, @ColumnName = columnName
        FROM #SurveyData
        WHERE @RowCount = rownum

        SET @sql = 'INSERT INTO #results ' +
                    ' SELECT s.SurveyId
                        , s.InstanceId
                        , CASE WHEN columnName = ''' +  @ColumnName + ''' THEN REPLACE(columnName, ''Q_'', '''') ELSE '''' END as QuestionNumber
                        , Cast(s.' + @ColumnName + ' as varchar(1000)) as ''Response''
                    FROM #SurveyData t 
                    INNER JOIN ' + @TableName + ' s' +
                    ' ON REPLACE(t.tableName, ''Library_'', '''') = s.SurveyID ' +
                    ' WHERE t.columnName = ''' + @ColumnName + ''''

        exec(@sql)

        SET @RowCount = @RowCount + 1       
    END

    SELECT SurveyId, InstanceId, QuestionNumber, Response
    FROM #results

drop table #SurveyData
drop table #results

    请参阅带有最终脚本的SQL Fiddle

    • 2

相关问题