Início / dba / Perguntas / 21046
Accepted
Taryn
Asked: 2012-07-18 14:08:02 +0800 CST

Stored Procedure para retornar dados de tabela criados dinamicamente

  • 772

História rápida, estamos trabalhando com um fornecedor externo que possui um sistema de pesquisa. O sistema não é necessariamente o melhor quando você cria uma nova pesquisa e o sistema cria uma nova tabela, ou seja:

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)

As tabelas são geradas com o SurveyIdno final do nome ( Library_) e as colunas Question são geradas com o QuestionIdno final ( Q_). Para esclarecer, as perguntas são armazenadas em uma tabela separada para que, embora os IDs das perguntas sejam sequenciais, eles não comecem em 1 para cada pesquisa. As colunas de perguntas serão baseadas no id atribuído a elas na tabela.

Parece simples o suficiente para consultar, exceto que precisamos extrair os dados de todas as tabelas de pesquisa para serem enviadas para outro sistema e é aí que entra o problema. Já que as tabelas são criadas automaticamente quando uma nova pesquisa é adicionada pelo front- aplicação final, o outro sistema não pode lidar com esse tipo de estrutura. Eles precisam que os dados sejam consistentes para serem consumidos.

Então, fui encarregado de escrever um procedimento armazenado que extrairá os dados de todas as tabelas do Survey e os colocará no seguinte formato:

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

Ao ter os dados de todas as tabelas no mesmo formato, eles podem ser consumidos por qualquer pessoa, não importa quantas tabelas e perguntas de pesquisa existam.

Eu escrevi um procedimento armazenado que parece estar funcionando, mas estou me perguntando se estou faltando alguma coisa ou se há uma maneira melhor de lidar com esse tipo de situação.

Meu código:

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)

Eu criei um SQL Fiddle com alguns dados de exemplo e o código.

Existe uma maneira diferente de escrever esse tipo de consulta? Há algum problema perceptível com ele?

Infelizmente, há muitas incógnitas com isso... quantas mesas teremos e quantas perguntas por pesquisa. Eu diria que teremos entre 25 a 50 pesquisas, com 2 a 5 perguntas cada.

sql-server sql-server-2008-r2

1 respostas

  1. Best Answer

    Com base nos comentários das pessoas no chat, decidi alterar um pouco meu script para INSERT INTOuma tabela temporária em vez de criar uma instrução SQL longa para executar no final. Então, no final, meu procedimento armazenado contém o seguinte:

    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

    Veja SQL Fiddle com o script final

    • 2

relate perguntas