SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 select table columns dynamically from select state
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

asela115
Starting Member

1 Posts

Posted - 02/24/2013 :  13:24:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 02/24/2013 :  16:16:54  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 02/24/2013 :  23:24:33  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000