Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    139

    Unanswered: variable in a select statement

    Is there anyway to use a variable to define a column in a select statement. I can put the variable in but I'm sure it will be read as a literal instead of the column.

    select @column_name from table
    Thanks,
    Bill

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can kludge this using dynamic SQL, but that opens up many HUGE cans of worms (such as SQL Injection). What are you trying to accomplish (from a business perspective)? There are almost always better ways to get the job done.

    -PatP

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not saying it is correct (Pat is right about reexamining your needs) but if I were to do this I would do something like:
    Code:
    CREATE TABLE dbo.MyTable
     (
     MyCol VarChar(1)
     )
    GO
     
    DECLARE @Column_Name AS VarChar(20), 
     @SQL AS VarChar(200), 
     @Loopy AS Int
     
    WHILE COALESCE(@Loopy, 0) < 2 BEGIN
     
     IF @Loopy IS NULL BEGIN
      SET @Column_Name = 'MyCol'
     END
     ELSE BEGIN
      SET @Column_Name = 'MyFakeCol'
     END
     
     IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = @Column_Name AND TABLE_NAME = 'MyTable') BEGIN
      SET @SQL = 'SELECT @Column_Name' + CHAR(13) +
        'FROM dbo.MyTable'
      SET @SQL = REPLACE(@SQL, '@Column_Name', @Column_Name)
     END
     ELSE BEGIN
      SET @SQL =  'POTENTIAL INJECTION'
     END
     
     PRINT @SQL + CHAR(13) + CHAR(13)
     
     SET @Loopy = COALESCE(@Loopy, 0) + 1
    END
     
    DROP TABLE dbo.MyTable
    BTW - why does my formatting from QA not follow through into my posts properly? VB formatting sticks just fine.
    Last edited by pootle flump; 09-07-06 at 04:38.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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