Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 select table columns dynamically from select state

Author  Topic 

asela115
Starting Member

1 Post

Posted - 2013-02-24 : 13:24:58
Hi,

How can i select columns of a table dynamically through a select statement if the column name need to be changed based on the value selected in a Combo Box in a different form.

it's like select column A from table X if combo box selection is A, select column B from same table if combo box selection is B etc..

Please assist

Regards,
asela115

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-24 : 16:16:54
If all columns in the table are of the same data type (or compatible data types), then you can use a case expression like in the example below:
DECLARE @colname NVARCHAR(64) = 'col2';
SELECT
CASE @colname
WHEN 'col1' THEN id1
WHEN 'col2' THEN id2
WHEN 'col3' THEN id3
END
FROM
YourTableName;
If the columns are incompatible data types, then you would have to use dynamic SQL. Dynamic SQL is susceptible to SQL inject attacks; The IF EXISTS clause in the query is to guard against that:
DECLARE @colname NVARCHAR(64) = 'col7';

DECLARE @sql NVARCHAR(4000);
SET @sql = 'SELECT '+@colname+ ' FROM YourTableName';
IF EXISTS
( SELECT * FROM INFORMATION_SCHEMA.[COLUMNS] c
WHERE c.TABLE_NAME = 'YourTableName'
AND c.COLUMN_NAME = @colName
)
EXEC (@sql);
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-24 : 23:24:33
even if they are of different datatypes you can cast them to sql_variant if you want to use CASE...WHEN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -