September 5, 2014 at 9:50 am
0 down vote favorite
I am giving an example scenario here. I have a database.
/*
I have a database. It has a table called [Property].
*/
GO
create table dbo.Property
(
PropertyID int identity(1,1)
,PropertyNumber varchar(50)
,AddressLine1 varchar(50)
,AddressLine2 varchar(50)
,Locality1 varchar(50)
,Locality2 varchar(50)
,Postcode varchar(50)
,PropertyPrice int
)
GO
insert into dbo.property(PropertyNumber, AddressLine1, AddressLine2, Locality1, Locality2, Postcode, PropertyPrice)
select '1000', 'Malibu Road', 'Malibu','CA','USA', '99999', 100 union
select '2000', 'Malibu Road', 'Malibu','CA','USA', '99992', 200 union
select '3000', 'Malibu Road', 'Malibu','CA','USA', '99989', 300 union
select '4000', 'Malibu Road', 'Malibu','CA','USA', '98889', 400 union
select '5000', 'Malibu Road', 'Malibu','CA','USA', '90889', 500
/*
Table [Label] to hold front-end labels for fields
*/
GO
create table dbo.label
(
label_id int identity(1,1)
,table_name varchar(50)
,field_name varchar(50)
,field_label varchar(50)
)
GO
insert into label(table_name, field_name, field_label)
select 'Property', 'PropertyNumber', 'House No' union
select 'Property', 'AddressLine1', 'Street' union
select 'Property', 'AddressLine2', 'City' union
select 'Property', 'Locality1', 'State' union
select 'Property', 'Locality2', 'Country' union
select 'Property', 'Postcode', 'ZIP' union
select 'Property', 'PropertyPrice', 'Value'
GO
/*
A table [Option] for holding certain business rules.
Front-end applications use this table to decide which columns
(and in which order) to show results in grid view.
User can change the value on option_value column.
*/
GO
create table dbo.[Option]
(
option_id int identity(1,1)
,option_desc varchar(50)
,option_value varchar(1000)
)
GO
insert into [Option](option_desc, option_value)
select 'PropertyAddress', ',PropertyNumber,AddressLine1,Locality1,Postcode'
go
Is there a way to create a view/table-valued function that can return property details with result set containing columns from the [Option] table and aliases from [label] table. The goal is to some sort of view/TVF that the front-end application can use without having figure out columns, their order and alias.
I have used stored procedure with dynamic SQL and sp_executesql for this purpose successfully. Howvere, If I can have some sort of VIEW or TVF I can use it to JOIN with other tables. As far as I know this JOINing is not possible with stored procedure. Views and Functions do not allow dynamic queries - so I am restricted here. I would like a view or TFV or something that will return something like:
select PropertyNumber as [House No]
,AddressLine1 as [Street]
,Locality1 as [State]
,Postcode as [ZIP]
from property
This will change based on the list of columns from table [Option] and the column header from table [Label].
September 5, 2014 at 10:49 am
Dynamic sql is the only option here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply