How to get Column value without knowing column name ? SQL Server

Question

I have table name as @Table_Name

I have column value as @Value but don't have the column name (but that exist at 1st position and can be Seek_id or prov_id ...I have to compare my value with this id )

How can I compare that table column name value ?

I want something like

SELECT * FROM @Table_Name
WHERE Table.Column[1].Value = @Value

for example @Table_Name = bb_match and @Value = 6

Solution

Possible this be helpful for you -

Query:

IF OBJECT_ID (N'dbo.bb_match') IS NOT NULL
   DROP TABLE dbo.bb_match

CREATE TABLE dbo.bb_match (seek_id INT, prov_id INT)

INSERT INTO dbo.bb_match (seek_id, prov_id)
VALUES (6, 1), (2, 6) 

DECLARE 
      @ColumnID TINYINT
    , @Value INT 
    , @TableName SYSNAME
    , @SQL NVARCHAR(500)

SELECT 
      @ColumnID = 1
    , @Value = 6
    , @TableName = 'dbo.bb_match'

SELECT @SQL = 'SELECT * FROM ' + @TableName + ' WHERE [' + c.name + '] = ' + CAST(@Value AS NVARCHAR(MAX))
FROM sys.objects o WITH (NOWAIT)
JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
JOIN sys.columns c WITH (NOWAIT) ON o.[object_id] = c.[object_id]
WHERE o.[type] = 'U' -- <-- only for tables columns
    AND s.name + '.' + o.name = @TableName
    AND c.column_id = @ColumnID

PRINT @SQL

EXEC sp_executesql @SQL

Shorter, but unsafe (sys.columns contains column_name for tables, views, procedures, ...):

SELECT @SQL = 'SELECT * FROM ' + @TableName + ' WHERE [' + c.name + '] = ' + CAST(@Value AS NVARCHAR(MAX))
FROM sys.columns c WITH (NOWAIT)
WHERE c.[object_id] = OBJECT_ID(@TableName)
    AND c.column_id = @ColumnID

EXEC sys.sp_executesql @SQL

Output:

SELECT * FROM dbo.bb_match WHERE [seek_id] = 6

Results:

seek_id     prov_id
----------- -----------
6           1

Comments

[marc_s]

You cannot do this - SQL Server needs a column name - it cannot use ordinal position to refer to a column in a table

[Jaloopa]

You may be able to use some dynamic SQL querying sys.columns to find the first column

[Maximus]

you can use the ordinal_position field in the INFORMATION_SCHEMA.COLUMNS table to find the first column of the given table.

[KPSingh]

this is near to my solution but not showing value only showing column name of that row ..(sent by my friend):

[KPSingh]

DECLARE @column_name varchar(20) set *column_name = (SELECT top 1 column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'bb_match') select * from bb_match where *column_name = '8' (*=@)

[Pondlife]

possible duplicate of Column-name and/or table-name as parameters and numerous other similar questions

[Maximus]

Where did you specify the column position. he want to compare the value with tthe first column.

[KPSingh]

I have table name *table_Name and column value *value not column name ...How can I search in *table_Name I know that column position is 1st( 1st column in table ) .....

[KPSingh]

I have value which I have to compare with 1st column in the table that is id ( name is anything like seek_id or prov_id)

[KPSingh]

thanks jaloopa .... but getting error Msg 137, Level 15, State 1, Line 2 Must declare the scalar variable "@sql". Msg 137, Level 15, State 2, Line 7 Must declare the scalar variable "@sql".

[KPSingh]

select @sql = 'SELECT * FROM ' + bb_match + ' WHERE ' + name + ' = ' + 8 from sys.columns where object_id = object_id('TCONTROL') and column_id = 1 exec (@sql)

[KPSingh]

Devart thanks for your ans ...SELECT * FROM dbo.bb_match WHERE [seek_id] = 6 ( but my column id is not seek_id ....that can be any think like seek_id or prov_id ) means I will search in any table ...1st column value I have only table name ....and value for campare with 1st column but dont know 1st column name ..please help me ..its very urgent

[Jaloopa]

Must declare the scalar variable "@sql" means you didn't include the first line. Fill in the required table name and value and run the entire script

[Devart]

Yippee! & you're welcome @user2369561 :))). In case this solution fully suits you, I would be glad if you could confirm it.

[Jaloopa]

I've modified the query and added some explanation. If you want to see what the final query looks like, replace EXEC(@sql) with SELECT @sql

[KPSingh]

I was trying to write one SP that will take any table name and any id then search that table and compare 1st column with my id then show then result / row ....