April 17, 2008 at 4:31 am
Dear ALL,
I have one senario.
Create table ColumnDetails
(
ID int,
ColumnName varchar(4000)
)
Insert into ColumnDetails
Select 1,'Select A.Eno,A.Ename,B.Dept from Emp a,Dept b where a.Eno = b.Eno'
Union All
select 2,'Select Eno,Ename,DOB from Emp where Eno = @Eno'
Union All
select 2,'Select Dept,DeptDesc from Dept where DeptId = @DeptId'
A new window has been generated to show the result.
Say for example,
Assume, There is one combo box which will list out the ID number. Most probably, They will choose 1 only. occasionally they choose 2 or 3.
If they choose 1, then a stored procedure will be called with parameter 1.
--------
Create procedure P1
(
@ID int
)
as
Begin
Declare @SQL varchar(4000)
select @SQL = ColumnName from ColumnDetails
where ID = @ID
Exec (@SQL)
End
------
Output format:
Eno Ename Dept
1 Karthik CS
2 Guna FF
3 Ravi DD
4 Kamal PP
5 Siva LL
But,End users have the option to introduce a new column in the report.like,
Output Format:
Eno Ename Dept DOB
Here DOB is the new column, But our query will not retrieve the DOB from EMP table.
Till now they just open the above table and add the new column like
Select A.Eno,A.Ename,B.Dept,A.DOB from Emp a,Dept b where a.Eno = b.Eno
But now they dont prefer to do it manually.
Our team will have to add the DOB column dynamically.
Thing is we dont know that new column is stored in which table,so first we need to identify the table(whether Emp or Dept)which have this new column,then we have to give the alias name.
The following statement has to be generated dynamicaly.
Select A.Eno,A.Ename,B.Dept,A.DOB from Emp a,Dept b where a.Eno = b.Eno
Experts inputs are really welcome!
karthik
April 17, 2008 at 5:53 am
[font="Verdana"]Hey... I found the column DOB. It is there in the EMP table 😀
Union All
select 2,'Select Eno,Ename,DOB from Emp where Eno = @Eno'
Union All
what else you want?
Mahesh
[/font]
MH-09-AM-8694
April 18, 2008 at 3:21 am
Any Inputs ?
karthik
April 21, 2008 at 2:47 am
Any input?
karthik
April 22, 2008 at 2:54 am
It Would be highly appreciable if anybody help me to solve my issue.
karthik
April 22, 2008 at 3:17 am
Karthik
It looks to me as if it would be quicker and easier to do this with views. But if you insist on doing it like this, please tell us how the end users choose the additional column? Do they select it from a drop-down list? Or do they enter it in a free-text field?
John
April 22, 2008 at 4:13 am
They will choose it from a dropdown list.
karthik
April 22, 2008 at 4:27 am
In which case the application logic that generates the list will also have the name of the table. It will therefore be fairly simple to generate the select statement dynamically, something like this:
SET @SQL = 'SELECT A.col1, B.col1, ' +
CASE @TableName WHEN 'Table1' THEN 'A'
WHEN 'Table2' THEN 'B'
END +
'.' + @ColumnName +
' FROM Table1 A JOIN Table2 ON A.ID = B.ID'
John
April 22, 2008 at 4:51 am
John Thanks.
But they dont have the option to choose the table name. Only Drop down box will show the list of columns. In that case, i think we need to identify the table name of that particular column first.
How should we handle that situation ?Shall we go for dynamic Sql ?
karthik
April 22, 2008 at 5:04 am
Karthik
The easiest way is to have the application to give you the table name. It must have used something like "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN 'Table1', 'Table2'" in order to generate the list. If you change that to "SELECT TABLE_NAME + '.' + COLUMN_NAME FROM..." then you're sorted. If you can't do that, then use the INFORMATION_SCHEMA.COLUMNS view to determine which table the column is in. What happens if the user chooses a column name that is common to both tables, if there are any?
John
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply