2

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

KPSingh
  • 489
  • 1
  • 3
  • 7
  • 2
    You cannot do this - SQL Server needs a column name - it cannot use ordinal position to refer to a column in a table – marc_s May 10 '13 at 10:08
  • 1
    You may be able to use some dynamic SQL querying sys.columns to find the first column – Jaloopa May 10 '13 at 10:09
  • you can use the ordinal_position field in the INFORMATION_SCHEMA.COLUMNS table to find the first column of the given table. – Maximus May 10 '13 at 10:18
  • this is near to my solution but not showing value only showing column name of that row ..(sent by my friend): – KPSingh May 10 '13 at 10:35
  • 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' (*=@) – KPSingh May 10 '13 at 10:36
  • possible duplicate of [Column-name and/or table-name as parameters](http://stackoverflow.com/questions/5791764/column-name-and-or-table-name-as-parameters) and numerous other [similar questions](http://stackoverflow.com/search?q=sql+server+column+table+name+variable) – Pondlife May 10 '13 at 13:54

2 Answers2

2

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
Devart
  • 119,203
  • 23
  • 166
  • 186
  • Where did you specify the column position. he want to compare the value with tthe first column. – Maximus May 10 '13 at 10:16
  • 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 May 10 '13 at 10:20
  • 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 May 10 '13 at 10:30
  • 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 – KPSingh May 10 '13 at 10:49
  • Yippee! & you're welcome @user2369561 :))). In case this solution fully suits you, I would be glad if you could confirm it. – Devart May 10 '13 at 11:13
  • 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 .... – KPSingh May 11 '13 at 15:21
0
declare @sql varchar(MAX)
declare @tablename varchar(100) = 'MyTable' --add your table name here
declare @value varchar(100) = 'SomeValue' -- add your desired value hree

select @sql = 'SELECT * FROM ' + @tablename + ' WHERE '
+ name 
+ ' = ''' + @value + ''''
from sys.columns where object_id = object_id(@tablename) and column_id = 1

exec (@sql)

There are three parts to this. First I'm declaring three strings. @sql is where I will build up the query, @tablename and @value are the table and search value to look in/for. I've put in the dummy values MyTable and SomeValue to show what I'm talking about

Next I build up the sql statement. The first line sets the string as SELECT * FROM MyTable WHERE

I then add in the column name by selecting Name from the SQL SERver system table sys.columns, filtering on the first column (column_id = 1) and the table name

The next step is to add the value we want to search for in the column.

Finally, EXEC(@sql) interprets the string as a command and runs it.

Jaloopa
  • 722
  • 1
  • 6
  • 21
  • 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 May 10 '13 at 10:43
  • 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 May 10 '13 at 10:43
  • 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 – Jaloopa May 10 '13 at 10:52
  • 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 – Jaloopa May 10 '13 at 11:22