儲存過程返回動態創建的表數據

簡短的背景故事，我們正在與擁有調查系統的外部供應商合作。當您創建新調查並且系統創建新表時，系統不一定設計得最好，即：

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 個問題。

根據聊天中人們的評論，我決定將我的腳本稍微更改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

引用自：https://dba.stackexchange.com/questions/21046