Temporary Table Column Name

  • I there a way to get the column name from a temporary table?

    SELECT COL_NAME(OBJECT_ID('#temptable'), 1)

    does not work on temporary table.

  • Just curious why you would need to do this?  You create the temp table through code, so you should know what the columns in it are. 

  • Same question.. but I couldn't find a work around that didn't use the system tables directly :

    create table #temp (name varchar(20) not null)

    Select OBJECT_ID('tempdb.dbo.#temp')

    Select name from tempdb.dbo.SysColumns where id = OBJECT_ID('tempdb.dbo.#temp') and Colid = 1

    --name

    DROP TABLE #temp

  • here is another way... ugly but you shouldn't need this anyways

    USE tempdb

    SELECT COL_NAME(OBJECT_ID('#temptable'), 1)

    USE LocalDBName


    * Noel

  • You ask WHY would I want to know the column name of a table that was just created? To make a long story short, the limitations of report services is causing me to try some crazy crazy things!

    I created a one record temp table with all of the field names as course names (i.e Math, Science etc.) When I find a student has passed a course I wanted to turn the Math flag on so that when this one record is sent to report services I can make the appropriate icon visible when ever the flag is on.

    Anyway because I can't do this, (see below) 

    select @colname= 'Math'  -- I am looping thru the student records and Math is a subject passed

    update #temptable set @colname=1 where @colname = @colname

    I am going to have to create a very very long case statement for now.

    Thanks everyone for you help.

    Marie

     

  • I am not an specialist in Reporting services but I believe you are approaching the problem the wrong way. You should be able to perform a simple select returning a list of the passed subjects and in the Report pivot that information.

     


    * Noel

  • I created a pivot/matrix in the report but I was not turn able to turn the image on or off based on the value in the matrix. It was a good suggestion even though it did not work. Any other suggestions???

    Marie

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply