Using views

Question

Using views

Iggy-SQL

SSCarpal Tunnel

Points: 4169
More actions
January 15, 2006 at 4:44 pm

#103040

I've got this problem and I need some ideas to solve it.
My company purchased this new software which can show GIS map on the web dynamically, which is excellent! However, this new software can only either read a view or read a table as the data source.
Now I've got this many tables that I need to join for the data source, and it involves some cross tab.
It's easy to create a cross tab using stored procedure, but how can I present it as a view?
I don't really want to use a table, because then the table need to be constantly refreshed, especially considering the data changes all the time and the application is accessed all the time as well!
Any ideas are appreciated. Thanks in advance.
Urbis, an urban transformation company

Viewing 5 posts - 1 through 4 (of 4 total)

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

Ian Yates SSCoach Points: 19738 More actions · Answer 1

What about a view that selects from a user-defined function? In the UDF you can create your table variables, perform procedural logic and then create a view that does a

select * from dbo.MyUDF

Cheers

Iggy-SQL SSCarpal Tunnel Points: 4169 More actions · Answer 2

Thanks Ian! I didn't remember that UDF can return a table.

I've done some test, can't seem to get what I want. Problem is with the UDF, the Inline table-valued function can't seem to have any codes in it other than return (select statement) and it also cannot use return exec myStoredProc. I can't use Multi-statement table-valued function as well because the final table structure is dynamic (i.e., the fields in the output table may vary between different record type - so if user asks for record type A, output table may have 10 fields, but if user asks for record type B, output table may have 5 fields; plus there may be additional fields added in the future to a certain record type)

Can you help? I can elaborate more if that will help. Thanks.

Urbis, an urban transformation company

Ian Yates SSCoach Points: 19738 More actions · Answer 3

A UDF must be written so that it has no side-effects. This means that you cannot do things like execute stored procs unfortunately... It makes sense though because this lets MSSQL execute the UDF as many times as it desires knowing that no side-effects could happen (eg, once per row for a scalar-valued function).

With regards to your different table outputs, how were you planning to represent all of these with a single view? I can only think you could combine all possible columns into one wide return table with the very first field being some sort of table type indicator. Then you can select the appropriate data using a multi-statement function and have nulls for the fields that doesn't apply....

Hope that helps - let me know if I'm off base!

Iggy-SQL SSCarpal Tunnel Points: 4169 More actions · Answer 4

Well, the function can take the record type as the variable. But I don't mind creating views for each different type of record, however, the number of extra fields for each record can be different and there's no way for me to know for sure unless I query the table.

Let's say we have this main table called Store with fields: StoreID, StoreName, StoreType.

Then we have another table called ExtraFields with fields: ExtraFieldID, ExtraFieldName.

And then we have another table called ExtraFieldsStoreType with fields: ExtraFieldID, StoreType.

Now we also have another table, called StoreExtraFields with fields: StoreID, ExtraFieldID, ExtraFieldData.

Now the contents are as follows:

Store

StoreID StoreName StoreType

==========================

1 ABC A

2 DEF B

ExtraFields

ExtraFieldID ExtraFieldName

======================

1 Turnover

2 Floorspace

3 Rent

4 Product Group

ExtraFieldsStoreType

ExtraFieldID StoreType

==================

1 A

1 B

2 A

3 B

4 B

StoreExtraFields

StoreID ExtraFieldID ExtraFieldData

===========================

1 1 $100,000

1 2 500 sqm

2 1 $150,000

2 3 $50,000

2 4 Women's Apparel

As you can see, StoreID 1 and 2 have different fields. The ExtraFields table may grow, so it is dynamic, and not all stores will have the same extra fields.

What I'm thinking, is what if I create the function dynamically instead? The function can be created every 15 minutes or so. Plus I know for sure the extra fields won't be added that often. Or I can create a trigger on the ExtraFields table on Insert, and call the stored procedure to create a new function and thus eliminating the need for scheduled jobs. Will this be possible?

Urbis, an urban transformation company