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

Answer 1

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