3

Possible Duplicate:
Is it possible to select sql server data using column ordinal position

Not sure if the title help, but i'm trying to create a general function and i was wondering if i could use a column position in my WHERE statements instead of a column name. So for example

SELECT * FROM table WHERE myID=1

would become something like this

SELECT * FROM table WHERE [0]=1

Does this make sense? is it doable? Thanks guys.

BTW - Using MSSQL 2005-2008

Community
  • 1
  • 1
Damien
  • 4,093
  • 9
  • 39
  • 52
  • 2
    already covered in this post: http://stackoverflow.com/questions/368505/is-it-possible-to-select-sql-server-data-using-column-ordinal-position – ssis_ssiSucks Jun 04 '12 at 15:09

2 Answers2

1

I think you must get column name of column position and generate execute query with column name. below code help you to get column name of column position and Execute your Query:

Declare @TableName  VarChar(100)
Declare @ColumnPosition int

Set @TableName = 'Table1'
Set @ColumnPosition = 1

Declare @ColumnName VarChar(250)
SELECT @ColumnName  = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName and ORDINAL_POSITIO = @ColumnPosition

Declare @Query VarChar(250)
Set @Query = 'Select * From ' + @TableName + ' Where '+ @ColumnName + ' = 1'
Exec (@Query)
mehdi lotfi
  • 11,194
  • 18
  • 82
  • 128
0

I think is not possible, but you can create your query dinamically in a string an run it with an EXEC.

    DECLARE @query VARCHAR(100)
    SET @query = 'SELECT * FROM Table WHERE field = ' + CONVERT(VARCHAR(10), 100)
    EXEC (@query)
crassr3cords
  • 280
  • 1
  • 7