Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65

    Unanswered: CREATE a Temp Table via "EXEC (@SQL)"

    I need to create a dynamic temporary table in a SP. Basically, I am using the temp table to mimic a crosstab query result. So, in my SP, I have this:

    Code:
    -------------------------------------------------------------------------------------
    -- Get all SubquestionIDs for this concept
    -------------------------------------------------------------------------------------
    DECLARE curStudySubquestions CURSOR LOCAL STATIC READ_ONLY FOR 
    SELECT        QGDM.SubquestionID,
                    QGDM.ShortName,
                    QGDM.PosRespValues
    FROM            RotationMaster AS RM
                    INNER JOIN RotationDetailMaster AS RDM ON RM.Rotation = RDM.Rotation
                    INNER JOIN QuestionGroupMaster AS QGM ON RDM.QuestionGroupNumber = QGM.QuestionGroupNumber
                    INNER JOIN QuestionGroupDetailMaster AS QGDM ON QGM.QuestionGroupNumber = QGDM.QuestionGroupNumber
    WHERE             RM.Study = @Study
    GROUP BY         QGDM.SubquestionID,
                    QGDM.ShortName,
                    QGDM.PosRespValues
    HAVING          QGDM.SubquestionID <> 0
    
    -------------------------------------------------------------------------------------
    -- Dynamically create a Temp Table to store the data, simulating a pivot table
    -------------------------------------------------------------------------------------
    SET @Count = 2
    SET @SQL = 'CREATE TABLE #AllSubquestions (Col1 VARCHAR(100)'
    
    OPEN curStudySubquestions
    FETCH NEXT FROM curStudySubquestions INTO @SubquestionID, @ShortName, @PosRespValues
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL = @SQL + ', Col' + CAST(@Count AS VARCHAR(5)) + ' VARCHAR(10)'
        SET @Count = @Count + 1
        FETCH NEXT FROM curStudySubquestions INTO @SubquestionID, @ShortName, @PosRespValues    
    END
    SET @SQL = @SQL + ', ShowOrder SMALLINT)'
    
    CLOSE curStudySubquestions
    PRINT 'Create Table SQL:'
    PRINT @SQL
    EXEC (@SQL)
    SET @ErrNum = @@ERROR
     IF (@ErrNum <> 0)
         BEGIN
         PRINT 'ERROR!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
         RETURN
         END
    PRINT '*** Table Created ***'
    
    -- Test that the table was created
    SELECT *, 'TEST' AS AnyField FROM #AllSubquestions
    The line PRINT @SQL produces this output in Query Analyzer (I added the line breaks for forum formatting):
    Code:
    CREATE TABLE #AllSubquestions 
    (Col1 VARCHAR(100), 
    Col2 VARCHAR(10), 
    Col3 VARCHAR(10), 
    Col4 VARCHAR(10), 
    Col5 VARCHAR(10), 
    Col6 VARCHAR(10), 
    Col7 VARCHAR(10), 
    ShowOrder SMALLINT)
    However, the SELECT statement to test the creation of the table produces this error:
    Code:
    *** Table Created ***
    Server: Msg 208, Level 16, State 1, Procedure
     sp_SLIDE_CONCEPT_AllSubquestions, Line 73
    Invalid object name '#AllSubquestions'.
    It appears that the statement to create the table works, but once I try to access it, it doesn't recognize its existance. Any ideas?
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    The "select *" is outside of the transaction that created the temp table. The way you have written the proc, the temp table disappears at the conclusion of the exec(@sql).

    I would suggest you create a unique static table name (not a temp table) that you feed to the exec sql, then reference the table later as needed. The do the right thing and clean-up after yourself before you exit the sproc.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65
    The problem is that this temp table will have a variable number of fields when it is run, so a static table won't work. It would be messy to figure out which fields contain data. Oh, and that is only a part of the SP, I clean up properly

    What I tried was creating "##Allsubquestions", and the table remains in scope for the rest of the SP.

    Maybe, though, there is a better way of doing this? Suppose this study has 4 questions, repeating once per Concept. I need the data in this temp table like this:
    Code:
    Col1        Col2 (Question1)    Col3 (Question2)    Col4 (Question3)    Col5 (Question4)    ShowOrder
    --------------------------------------------------------------------------------------------------------------------------
    Concept 1    25                    86                 15                  47                   1
    Concept 2    35                    89                 54                  72                   2
    Concept 3    69                    17                 48                  36                   3
    Norm A       50                    40                 40                  30                   100
    Norm B       54                    38                 42                  36                   101
    This SP will return a recordset to a generic routine that will populate a MS Graph in a PowerPoint presentation, so I need return a dataset that is formatted where I can just load the Graph's Datasheet object from the SP result set. What will change from study to study is the number of questions. This example has four. There may be 8, 10, or any other number.
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  4. #4
    Join Date
    Nov 2010
    Posts
    1
    Hi frd,
    These kind of problem we can create the temp table with the static fields and then we can alter the table by using the alter statement.

    let's see the following example,

    Create procedure sp_AL_Report ( @ali_month int, @ali_year int, @aldt_last_date datetime, @ali_fin_year int )
    As
    Begin

    Declare @ls_table_qry varchar(5000), @li_start_month int, @li_start_year int, @li_end_month int, @li_end_year int
    Declare @ls_month varchar(1000), @ldt_month_date datetime

    Declare @li_loop_month int, @li_loop_year int

    set @ls_table_qry = ''
    set @ls_month = ''

    Create Table #tmp_annual_leave ( Emp_no int, Open_leave decimal(18,2), Eligible decimal(18,2) )

    select @li_start_month = month(start_date) , @li_start_year = year(start_date) ,
    @li_end_month = month(end_date) , @li_end_year = year(end_date)
    from financial_dates
    where year = @ali_fin_year

    set @li_loop_month = @li_start_month
    set @li_loop_year = @li_start_year

    while (@li_loop_year * 100 ) + @li_loop_month <= (@li_end_year * 100 ) + @li_end_month
    Begin

    set @ldt_month_date = cast( cast(@li_loop_year as varchar(4)) + '/'+ cast(@li_loop_month as varchar(2)) + '/01' as datetime)
    set @ls_month = @ls_month + ' ' + left( datename(mm, @ldt_month_date),3) + ' int , '
    set @li_loop_month = @li_loop_month + 1

    if @li_loop_month > 12
    Begin
    set @li_loop_month = 1
    set @li_loop_year = @li_loop_year + 1
    end
    End

    set @ls_table_qry = @ls_table_qry + ' Alter table #tmp_annual_leave add '
    set @ls_month = left( @ls_month, len(@ls_month) - 1 )
    set @ls_table_qry = @ls_table_qry + @ls_month
    Exec ( @ls_table_qry )

    select * from #tmp_annual_leave
    End

    -------

    The output of the above procedure will create one temp table with the dynamic columns

    select emp_no, open_leave, Eligible, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec from #tmp_annual_leave

    Hopefully this solution may help for you.

    Thanks & Regards,
    Thiyagu.N

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What do you want to do with the temp table?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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