September 1, 2010 at 7:51 am
Hi,
I have a mapping table named TableMap that indentify some columns from some tables:
TableMap
Param | SchemaName | TableName | ColumnName
-------------------------------------------------
PA dbo Tab1 col1
PB dbo Tab1 col4
PC dbo Tab2 col2
PD dbo Tab3 col1
PH dbo Tab3 col3
PN dbo Tab3 col4
.................................................
I have also following tables:
Tab1
|col1 | col2 | col3 | col4 |
--------------------------
12 17 15 14
Tab2
|col1 | col2 | col3 |
--------------------
21 22 28
Tab3
|col1 | col2 | col3 | col4 |
--------------------------
31 35 36 34
[/code]
I have to create a stored procedure that has as input an xml of form:
<Params>
<PB/>
<PD/>
<PH/>
<PN/>
</Params>
and have to return a dataset with values from columns where PB, PD, PH and PN are mapped in TableMap.
So, in the above example the table returned by procedure should have column PB, PD, PH and PN and values from tables based on mapping from TableMap:
PB | PD| PH| PN|
-----------------
14 31 36 34
How can I implement such a logic?
September 1, 2010 at 12:21 pm
I'm not sure how to parse the XML you provided, so I modified it so I could parse it. You can modify the XML to match your layout
Setup
Create table tab1 (Col1 int, col2 int, col3 int, col4 int)
Create table tab2 (Col1 int, col2 int, col3 int)
Create table tab3 (Col1 int, col2 int, col3 int, col4 int)
insert into tab1 select 12, 17, 15, 14
insert into tab2 select 21,22,28
insert into tab3 select 31,35,36,34
create table TableMap
(ParamName char(2), SchemaName char(3), TableName char(4), ColumnName char(4))
INSERT INTO TableMap
select 'PA','dbo','Tab1','col1' union all
select 'PB','dbo','Tab1','col4' union all
select 'PC','dbo','Tab2','col2' union all
select 'PD','dbo','Tab3','col1' union all
select 'PH','dbo','Tab3','col3' union all
select 'PN','dbo','Tab3','col4'
Code
declare @x xml,
@syntax nvarchar(max)
set @x = '<Params>
<parm id="PB"/>
<parm id="PD"/>
<parm id="PH"/>
<parm id="PN"/>
</Params> '
select @syntax = 'select ' +
STUFF(
(
select ',' + '(select ' + tm.Columnname + ' from ' + tm.SchemaName + '.' + tm.TableName + ') ' + fileds.value('@id[1]', 'varchar(10)')
FROM@x.nodes('//Params/parm') as xmldata(fileds)
inner join TableMap tm
on fileds.value('@id[1]', 'varchar(10)') = tm.ParamName
for XML path ('')
), 1, 1, ''
)
exec sp_executesql @syntax
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 2, 2010 at 2:41 am
Thanks Mike01
You have answered my question.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply