Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2003
    Posts
    130

    Question Unanswered: Stored Procedure to list values from different tables

    I have several tables that are related with the same primary key that I need to make a consolidated list of values for.

    The schema of each table is similar but some have more or less fields than others. I want to make a list of all table names, field names, and the value of that field. I can select all tables included from sysobjects.

    The existence of the key is probable but not guaranteed in all tables.

    The basic table format is:

    Code:
    Table1
    ------
    ID    Field1    Field2    Field3
    1     0.0       4.1       3.9
    2     0.5       1.3       0.2
    3     7.1       8.8       9.3 
     
    Table2
    ------
    ID    Field1
    0     0.4
    1     3.3
    2     2.7
    3     5.7
     
    Table3
    ------
    ID    Field1    Field2
    2     2.4       4.6
    3     4.3       8.1
     
    Format of the result set:
    (specifying ID = 2)
     
    Table_Name    Table_Field    Value
    Table1        Field1         0.5
    Table1        Field2         1.3
    Table1        Field3         0.2
    Table2        Field1         2.7
    Table3        Field1         2.4
    Table3        Field2         4.6

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Based on the specification that you've given so far, this sounds like a disaster that is eagerly anticipating its next victim! This makes me nervous in SO many different ways.

    Can you explain in a bit more detail what "real world" behavior you are trying to produce? This sounds like a case of "design gone amok" that leads me to think that someone has over-analyzed the user's specification for some task, and come up with a solution that would make even Rube Goldberg nervous.

    -PatP

  3. #3
    Join Date
    Mar 2003
    Posts
    130
    What the user needs is a way to provide the key as criteria and retrieve every value associated with that key from multiple tables. I inherited this database and don't have much control over what the user is requesting. If you think the idea itself is horrendous, wait until you see the stored procedure that was written (by someone else) to do this now! I'm embarrassed to even post the convoluted mess.

    Code:
    CREATE PROCEDURE SP_REP_QA_DATA @AXLE_NO VARCHAR(30) AS
    BEGIN
    SET NOCOUNT ON
    DECLARE @TABLE_NAME AS VARCHAR(20),
            @STATION_NUMBER AS INT,
            @LINE_NUMBER AS INT,
            @TABLE_FIELD AS VARCHAR(40),
            @LOGICAL_FIELD_DESC AS VARCHAR(150),
            @SQL AS NVARCHAR(3000),
            @LH_WHEEL_END_SER_NO AS VARCHAR(40),
            @RH_WHEEL_END_SER_NO AS VARCHAR(40),
            @AXLE_LINE_NO AS CHAR(2),
            @WHEEL_END_LINE_NO AS CHAR(2),
            @VALUE AS VARCHAR(50)
     
        SET @AXLE_LINE_NO = 50
        SET @WHEEL_END_LINE_NO = 51
     
     SELECT @LH_WHEEL_END_SER_NO = PART_1,
            @RH_WHEEL_END_SER_NO = PART_2 
       FROM K1550_STA130 WITH(NOLOCK)
      WHERE AXLE_SERIAL_NO = @AXLE_NO
    --DELETE FROM TEMP_QA_DATA
    CREATE TABLE [#TEMP_QA_DATA] (
     [TABLE_NAME] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
     [TABLE_FIELD] [varchar] (40) COLLATE Latin1_General_CI_AS NULL ,
     [STATION_NUMBER] [int] NULL ,
     [LINE_NUMBER] [int] NULL ,
     [LOGICAL_FIELD_DESC] [varchar] (150) COLLATE Latin1_General_CI_AS NULL ,
     [VALUE] [varchar] (50) COLLATE Latin1_General_CI_AS NULL 
    ) ON [PRIMARY]
     
     SELECT SO.NAME, 
              SUBSTRING(SO.NAME,4,2)AS 'LINE_NAME'
              ,CONVERT(char, SUBSTRING(SO.NAME,10, 3))AS 'STATION_NUMBER'
         INTO #PRE_TABLE_INFO
         FROM [K1550_51].[DBO].[SYSOBJECTS] SO WITH(NOLOCK)
        WHERE SO.NAME LIKE 'K%'
          AND SO.NAME NOT LIKE '%_SECURITY'
     
     UPDATE #PRE_TABLE_INFO
        SET [STATION_NUMBER] = '80' 
      WHERE STATION_NUMBER = '80_'
     
       SELECT NAME, 
            LINE_NAME,
            CONVERT(int, STATION_NUMBER) as 'STATION_NUMBER'
         INTO #TABLE_INFO
         FROM #PRE_TABLE_INFO WITH(NOLOCK)
    BEGIN TRAN
      DECLARE CR1 CURSOR FOR 
     
        SELECT RTRIM(CONVERT(VARCHAR(20),[NAME])) AS [TABLE_NAME],
               STATION_NUMBER
          FROM #TABLE_INFO WITH(NOLOCK)
         ORDER BY LINE_NAME,STATION_NUMBER
      OPEN CR1
      FETCH NEXT FROM CR1 INTO @TABLE_NAME, @STATION_NUMBER
      WHILE @@FETCH_STATUS = 0 --FIRST LOOP
        BEGIN --FIRST LOOP
          DECLARE CR2 CURSOR FOR 
            SELECT LINE_NAME, 
                   TABLE_FIELD,
                   LOGICAL_FIELD_DESC
              FROM #TABLE_INFO TI WITH(NOLOCK),
                   ACTIVE_QA_FIELDS AQF WITH(NOLOCK)
             WHERE AQF.TABLE_NAME = TI.NAME  
               AND AQF.TABLE_NAME = @TABLE_NAME    
               AND AQF.STATUS = 1
             ORDER BY LINE_NAME,STATION_NUMBER
          OPEN CR2
          FETCH NEXT FROM CR2 INTO @LINE_NUMBER, @TABLE_FIELD,@LOGICAL_FIELD_DESC
          WHILE @@FETCH_STATUS = 0
            BEGIN
              SET @VALUE = ''       
              IF(@LINE_NUMBER = @AXLE_LINE_NO)
                 BEGIN
                    SET @SQL = 'SELECT @VALUE = ' + @TABLE_FIELD + CHAR(10) + 
                                ' FROM ' + @TABLE_NAME + ' WITH (NOLOCK)' + CHAR(10) +
                                'WHERE AXLE_SERIAL_NO = ' +   '''' + @AXLE_NO + '''' + CHAR(10) 
                    --PRINT @SQL    
                    EXEC SP_EXECUTESQL @SQL, N'@VALUE VARCHAR(50) OUTPUT', @VALUE OUTPUT
                    INSERT #TEMP_QA_DATA (TABLE_NAME, TABLE_FIELD,STATION_NUMBER, LINE_NUMBER, LOGICAL_FIELD_DESC, VALUE) 
                           VALUES (@TABLE_NAME, @TABLE_FIELD,@STATION_NUMBER,@LINE_NUMBER, @LOGICAL_FIELD_DESC, @VALUE)
     
                 END
              IF(@LINE_NUMBER = @WHEEL_END_LINE_NO)
                 BEGIN
                    SET @SQL = 'SELECT @VALUE = ' + @TABLE_FIELD + CHAR(10) + 
                                ' FROM ' + @TABLE_NAME + ' WITH (NOLOCK)' + CHAR(10) +
                                'WHERE WHEEL_END_SERIAL_NO = ' +   '''' + RTRIM(@LH_WHEEL_END_SER_NO) + '''' + CHAR(10) 
                    --PRINT @SQL    
                    EXEC SP_EXECUTESQL @SQL, N'@VALUE VARCHAR(50) OUTPUT', @VALUE OUTPUT
                    INSERT #TEMP_QA_DATA (TABLE_NAME, TABLE_FIELD,STATION_NUMBER, LINE_NUMBER, LOGICAL_FIELD_DESC, VALUE) 
                           VALUES (@TABLE_NAME, @TABLE_FIELD,@STATION_NUMBER,@LINE_NUMBER, @LOGICAL_FIELD_DESC, @VALUE)
                    SET @SQL = 'SELECT @VALUE = ' + @TABLE_FIELD + CHAR(10) + 
                                ' FROM ' + @TABLE_NAME + ' WITH (NOLOCK)' + CHAR(10) +
                                'WHERE WHEEL_END_SERIAL_NO = ' +   '''' + RTRIM(@RH_WHEEL_END_SER_NO) + '''' + CHAR(10) 
                    --PRINT @SQL    
                    EXEC SP_EXECUTESQL @SQL, N'@VALUE VARCHAR(50) OUTPUT', @VALUE OUTPUT
                    INSERT #TEMP_QA_DATA (TABLE_NAME, TABLE_FIELD,STATION_NUMBER, LINE_NUMBER, LOGICAL_FIELD_DESC, VALUE) 
                           VALUES (@TABLE_NAME, @TABLE_FIELD,@STATION_NUMBER,@LINE_NUMBER, @LOGICAL_FIELD_DESC, @VALUE)
     
                 END
              FETCH NEXT FROM CR2 INTO @LINE_NUMBER, @TABLE_FIELD,@LOGICAL_FIELD_DESC
            END
          CLOSE CR2
          DEALLOCATE CR2  
     
          FETCH NEXT FROM CR1 INTO @TABLE_NAME,@STATION_NUMBER
        END
      CLOSE CR1
      DEALLOCATE CR1  
     
    SELECT * FROM #TEMP_QA_DATA ORDER BY LINE_NUMBER,STATION_NUMBER
    COMMIT TRAN
    DROP TABLE #TABLE_INFO
    DROP TABLE #TEMP_QA_DATA
    DROP TABLE #PRE_TABLE_INFO
    END
    GO


    The table ACTIVE_QA_FIELDS lists the table name and column of every column needed. Every time schema changes this table has to be updated


    I'm using SQL Reporting Services which allows only one dataset per report. The user needs to pull one report that prints data from every table.

    Is it possible?
    Last edited by wey97; 01-20-06 at 19:15.

  4. #4
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47
    Try this :
    It utilize the undocumented xp_execresultset

    Code:
    create table #result
    (
        table_name    varchar(100),
        column_name    varchar(100),
        value        numeric(10,1)
    )
    
    create table #sql_cmd
    (
        cmdrow    int    identity(1,1),
        cmdtext    nvarchar(4000)    not null
    )
    
    declare
        @id    int
    
    select    @id = 2
    
    delete #sql_cmd
    delete #result
    insert into #sql_cmd(cmdtext)
    select  'INSERT INTO #result ' + 
        'SELECT ''' + object_name(c.id) + ''',''' + c.name + ''', '+ c.name + 
        ' FROM ' + object_name(c.id) + ' WHERE ID = ' + convert(varchar(10), @id)
    from    syscolumns c
    where    c.id    in (select id from syscolumns where name = 'ID')
    and    c.name    <> 'ID'
    exec master..xp_execresultset N'select cmdtext from #sql_cmd order by cmdrow', N'databasename'
    
    select * from #result
    -----------------
    KH


  5. #5
    Join Date
    Mar 2003
    Posts
    130
    Using what you provided on any database results in errors:
    used on pubs database

    Code:
    use pubs
    
    drop table #result
    drop table #sql_cmd
    
    create table #result
    (
        table_name    varchar(100),
        column_name    varchar(100),
        value        numeric(10,1)
    )
    
    create table #sql_cmd
    (
        cmdrow    int    identity(1,1),
        cmdtext    nvarchar(4000)    not null
    )
    
    declare @id    int
    select @id = 2
    
    delete #sql_cmd
    delete #result
    
    insert into #sql_cmd(cmdtext)
    select  'INSERT INTO #result ' + 
        'SELECT ''' + object_name(c.id) + ''',''' + c.name + ''', '+ c.name + 
        ' FROM ' + object_name(c.id) + ' WHERE ID = ' + convert(varchar(10), @id)
    from    syscolumns c
    where    c.id    in (select id from syscolumns where name = 'ID')
    and    c.name    <> 'ID'
    
    exec master..xp_execresultset N'select cmdtext from #sql_cmd order by cmdrow', N'pubs'
    
    select * from #result


    Errors:

    Code:
    (0 row(s) affected)
    
    (0 row(s) affected)
    
    (130 row(s) affected)
    
    Server: Msg 206, Level 16, State 2, Line 1
    Operand type clash: image is incompatible with numeric
    Server: Msg 260, Level 16, State 1, Line 1
    Disallowed implicit conversion from data type datetime to data type numeric, table 'tempdb.dbo.#result_____________________________________________________________________________________________________________0000000002F4', column 'value'. Use the CONVERT function to run this query.
    Server: Msg 260, Level 16, State 1, Line 1
    Disallowed implicit conversion from data type datetime to data type numeric, table 'tempdb.dbo.#result_____________________________________________________________________________________________________________0000000002F4', column 'value'. Use the CONVERT function to run this query.
    Server: Msg 260, Level 16, State 1, Line 1
    Disallowed implicit conversion from data type sql_variant to data type numeric, table 'tempdb.dbo.#result_____________________________________________________________________________________________________________0000000002F4', column 'value'. Use the CONVERT function to run this query.
    (0 row(s) affected)

  6. #6
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    This is a generic solution.
    Code:
    use pubs
    go
    create table ##temp1
    (
    TABLENAME varchar(200),
    FIELDNAME varchar(200),
    VALUE VARCHAR(7000)
    )
    GO
    
    declare @sql nvarchar(4000)
    declare @tableparameter nvarchar(1000)
    set @tableparameter='''stores'',''sales'''
    set @sql ='select ''insert into ##temp1 select ''''''+TABLE_NAME+'''''',''''''+COLUMN_NAME+'''''',
    ''+COLUMN_NAME+'' from ''+TABLE_NAME+'' where stor_id=7896'' from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME in('+@tableparameter+')'
     
    exec master..xp_execresultset @sql, N'pubs'
     
    go
     
    
    select * from ##temp1
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  7. #7
    Join Date
    Mar 2003
    Posts
    130
    Any way to make it more generic so you don't have to supply all the table names in @tableparameter ?

  8. #8
    Join Date
    Mar 2003
    Posts
    130
    keeping the @tableparameter my sql code is:
    Code:
    
    use K1550_51
    go
    
    
    drop table ##temp1
    
    create table ##temp1
    (
    TABLENAME varchar(200),
    FIELDNAME varchar(200),
    VALUE VARCHAR(7000)
    )
    GO
    
    declare @sql nvarchar(4000)
    declare @tableparameter nvarchar(1000)
    
    set @tableparameter=
    '''K1550_STA10'',''K1550_STA110'',''K1550_STA115'',''K1550_STA120'',''K1550_STA130'',''K1550_STA140'',
    ''K1550_STA150'',''K1550_STA160'',''K1550_STA170'',''K1550_STA180'',''K1550_STA190'',''K1550_STA195'',
    ''K1550_STA20'',''K1550_STA200'',''K1550_STA210'',''K1550_STA220'',''K1550_STA230'',''K1550_STA30'',
    ''K1550_STA40'',''K1550_STA45'',''K1550_STA50'',''K1550_STA60'',''K1550_STA70'',''K1550_STA80_1'',
    ''K1550_STA80_2'',''K1550_STA90'''
    
    set @sql ='select ''insert into ##temp1 select ''''''+TABLE_NAME+'''''',''''''+COLUMN_NAME+'''''',
    ''+ COLUMN_NAME +'' from ''+TABLE_NAME+'' WHERE Axle_Serial_No=''''1643306300060530103592'''''' from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME in('+@tableparameter+')'
     
    exec master..xp_execresultset @sql, N'K1550_51'
     
    go
     
    select * from ##temp1 order by tablename, fieldname, value
    And I receive this error:

    Code:
    Server: Msg 50000, Level 16, State 1, Procedure sp_execresultset, Line 198
    Internal Error : Command buffer limit of ~1MB has been reached! Resultset will not be executed.
    
    (0 row(s) affected)
    It relates to how many tables I list in @tableparameter. After I exceed about 14 tables the error occurs...
    Last edited by wey97; 01-23-06 at 15:54.

  9. #9
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    create a view and join with union all query with all tableS u required.pass that view into @tableparameter
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •