Post reply

SELECT Columns without using column names

  • September 28, 2006 at 8:12 am

    #90501

    Hi,

    I've been looking for a while on the net without much luck. What I'm trying to do is to find a way of retrieving a specific column (decided at run-time) from a table, but without using the exec command (performance is a concern).

    I'm going to assume an imaginary function GET_COLUMN() to explain what I need.

    DECLARE @ColumnName varchar(20)

    SET @ColumnName = 'Column1'

    SELECT GET_COLUMN(@ColumnName) FROM MyTable

    Asumming that MyTable contains an irrelevant amount of fields (Column1, Column2, Column2, etc.)

    Is there a way of achieving this in SQL Server 2000?

     

    Many Thanks!

    Marcelo

  • September 28, 2006 at 9:03 am

    #663033

    Not without dynamic sql.

    using exec sp_executesql, or EXEC()

    If you have not read, here is a great page concerning dynamic sql.

    http://www.sommarskog.se/dynamic_sql.html

    I am not an advocate of using dynamic sql to build your entire application on. But generally performance is not degraded terribly.

    If all your queries are built this way, then dynamic sql is not the way to go.

  • September 28, 2006 at 9:08 am

    #663037

    If you want to find out all the columns in a table, use the following

    CREATE TABLE #conv_tables_fields (

     TABLE_QUALIFIER NVARCHAR (128) NULL ,

     TABLE_OWNER NVARCHAR (128) NULL ,

     TABLE_NAME NVARCHAR (128) NULL ,

     COLUMN_NAME NVARCHAR (128) NULL ,

     DATA_TYPE SMALLINT NULL ,

     COL_TYPE_NAME NVARCHAR (128) NULL ,

     COL_PRECISION INT NULL ,

     LENGTH int NULL ,

     SCALE SMALLINT NULL ,

     RADIX SMALLINT NULL ,

     NULLABLE SMALLINT NULL ,

     REMARKS varchar (254) NULL ,

     COLUMN_DEF NVARCHAR (400) NULL ,

     SQL_DATA_TYPE SMALLINT NULL ,

     SQL_DATETIME_SUB SMALLINT NULL ,

     CHAR_OCTET_LENGTH INT NULL ,

     ORDINAL_POSITION INT NULL ,

     IS_NULLABLE VARCHAR (254) NULL ,

     SS_DATA_TYPE TINYINT NULL

    )

    INSERT #conv_tables_fields

    EXEC sp_columns @table_name = 'tablename'

    Then all your columns will be in the temp table #conv_tables_fields

  • September 28, 2006 at 9:14 am

    #663038

    The thing is that I'm actually having performance problems.

    I'm rewritting an audit trail system which is very sloppy and extremely hard to maintain (spaguetti code).

    So I decided to go for a generic trigger to log the entries on the audit tables.

    The problem arises when a generate the dynamic sql code to insert each modified column into the table.

    The AuditDetails table has three columns: AuditId (FK to main Audit table), ColumnName, Value (I just store the new value)

    I have the AuditId on a variable @AuditId and I'm dynamically retrieving the ColumnName by quering the db schema using the table name and the ordinal position, but I couldn't find a way to dinamically retrieve the actual value of the column on the "inserted" table, if you know what I mean.

    So is there absolutely no way of accessing the values of the columns without statically specifying the name? (e.g. Table1.Column1)? I was looking for something like COL_VALUE(int) or COL_VALUE(varchar), similarly to COL_NAME().

    My generic trigger actually takes twice the time that the previous one on the first execution. But the previous one takes a split second afterwards, and mine keeps on taking the same time over and over as it uses dynamic sql and the engine cannot predict nor parametrise what I'm going to execute each time.

    Any thoughts?

  • September 28, 2006 at 9:19 am

    #663042

    Thanks for your message. But what I'm trying to achieve in my code is to retrieve the values of a table and not the metadata. It's for the Audit Trail system that I described in my previous post.

    Thanks,

    Marcelo

  • September 28, 2006 at 9:22 am

    #663045

    BTW, thanks for the link, it looks very interesting. I'm reading it atm.

  • September 29, 2006 at 12:19 pm

    #663424

    Yes you can.  As long as you know the column names, and don't use IMAGE types.

    create table #foo (

    Type Varchar(50),

    Qty  Int,

    Notes Varchar(50))

    Insert #foo

    select 'apple', 3, 'Fruit'

    union

    select 'kangaroo', 4, 'Animal'

    union

    select 1,2,'penguin'

    declare @whichcolumn varchar(10)

    select @whichcolumn = 'first'

    select CASE @whichcolumn

     when 'first' then convert(sql_variant,Type)

     when 'second' then convert(sql_variant,Qty)

     when 'third' then convert(sql_variant,Notes) end as thecolumn

    from #foo

    drop table #foo

     

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

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