select entries of different columns of different tables using dynamic query

Question

I have two tables

studentTable

Id | Studentname | Adress

teacherTable

TID | TeacherName | Adress

In studentTable table I have columns Id and in teacherTable I have a column TID, while using dynamic query how do I select the entries regardless of column name.

select ID or PID from @Tablename

does not work, how can I do it, any idea?

The query which I tried:

SELECT  + '''' +  @TABLE_NAME + '''' + ',' + '''' +  @COLUMN_NAME + '''' + ',' + 'ID  + 
        ' FROM [' + @TABLE_NAME

You haven't explained the context. Why are you attempting to select from different tables using the same query? Why not have two different queries? See What is the XY Problem? — JDB
– JDB, Commented Oct 28, 2013 at 14:38
I am using this dynamic query in stored procedure,I need the output of all tables in one table which I got it working .Every table has ID only TeacherTable has TID..if I write two query any one query will give error,I dont know how to resolve.if you think this problem can be solved using two query please leave the code — user2857662
– user2857662, Commented Oct 28, 2013 at 14:45

JDB · Accepted Answer · 2013-10-28 16:58:00Z

1

You don't need a dynamic query, that would be slow.

Here is how you do it, two queries unioned together.

SELECT 'student' as [type], ID as [ID], studentname as name, address
from studentTable
where ID = @inID

union all

SELECT 'teacher' as [type], TID as [ID], teachername as name, address
from teacherTable
where TID = @inID

edited Oct 28, 2013 at 16:58

JDB

26.1k5 gold badges81 silver badges132 bronze badges

answered Oct 28, 2013 at 14:45

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user2857662 Over a year ago

I need to select ID where @columnname='rasa'

user2857662 Over a year ago

ex:select ID or TID from @TABLENAME where @COLUMNNAME='rasa'

user2857662 Over a year ago

I need query either to select ID or TID,when cursor is inside it shoud execute if the column name is ID,or PID

Hogan Over a year ago

@user2857662 - stop using cursors. cursors are not SQL, that way lies madness... learn how SQL works.

Hogan Over a year ago

@user2857662 - I changed the query to select both ID and TID.

Collectives™ on Stack Overflow

select entries of different columns of different tables using dynamic query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related