COMPLEX QUERY --select a coloumn name as value and using the value in a SELECT query.

Question

COMPLEX QUERY --select a coloumn name as value and using the value in a SELECT query.

hemanth T

SSC Eights!

Points: 807
More actions
July 3, 2011 at 7:13 am

#144125

Hi All,
Using the TABLE_NAME OR COLOUMN NAME from the result of a query and Using THESE values in a SELECT query getting the value in the COLOUMN.
I got a scenario where i get the value(COLOUMN_NAME) from a query and using this value(COLOUMN_NAME) in a select query i need to get the value present in the COLOUMN_NAME.
In the same way above,i have to get TABLE_NAME and use this to the above query.
Finally the structure shd be like this
COLOUMN_NAME=(select value1 from A)
TABLE_NAME=(select value2 from A)
select COLOUMN_NAME from TABLE_NAME
or
select (select value1 from A) from (select value2 from A)
but sql server shows errors.
Can Anyone help me out. Thanks in Advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Look up sys.all_columns and sys.all_objects in Books On Line ... you will find your answer there. But may I ask, why do you need to do this?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

geoff5 Hall of Fame Points: 3560 More actions · Answer 2

Here is a bit of dynamic SQL that should achieve the results you are looking for. I used your own suggested subquery in the places to populate the values of the @ColumnName and @TableName variables.

declare @SQL as varchar(100), @ColumnName as varchar(100), @TableName as varchar(100);

set @ColumnName = (select Value1 from A);

set @TableName = (select Value2 from A);

set @SQL = (select 'select ' + c.name + ' from ' + s.name + '.' + o.name from sys.all_columns as c inner join sys.all_objects as o on c.object_id = o.object_id inner join sys.schemas as s on o.schema_id = s.schema_id where c.name = @ColumnName and o.name = @TableName);

print @SQL;

exec (@SQL);

Hope this helps!

hemanth T SSC Eights! Points: 807 More actions · Answer 3

Hi,

Thank you very much for the code you suggested,i was searching for a solution since some time.

I used it by some improvising the code and it is working fine. 🙂

Thanks,

Hemanth

hemanth T SSC Eights! Points: 807 More actions · Answer 4

Hi Ron,

Thank you for your valuable suggestion.I learnt many technical issues.I got the solution from other forum member and it is working fine.:-)

Thanks,

Hemanth.

nigel. SSChampion Points: 11636 More actions · Answer 5

I've got to ask, where are the values for the column and table name coming from?

If they are entered by a user then you have opened yourself up to an SQL injection attack.

See here

Nigel
How to post questions

geoff5 Hall of Fame Points: 3560 More actions · Answer 6

Nigel,

I am unsure how this query, despite the fact that it generates a dynamic SQL statement, is vulnerable to a SQL injection attack. The values in the dynamically generated statement are pulled directly from the system tables and are not directly provided from the user input. If the values provided are not legitimate column and table values, would that not simply make the dynamic statement become a null value? Is that enough to qualify as an "attack"?

I would entirely agree to a vulnerability if the dynamic SQL had been something like: 'select ' + @ColumnName + ' from ' + @TableName, but the extra layer of validation by looking up the values in the system tables is meant to preclude malicious variable values from becoming a factor.

Having said all that, I am certainly open to the possibility that I am missing something that could cause an attack.

nigel. SSChampion Points: 11636 More actions · Answer 7

Geoff,

My bad. I hadn't noticed the use of system tables in your query (didn't scroll right far enough). 😀

Nigel
How to post questions