locked
Dynamically Create A View RRS feed

  • Question

  • Log-in access web-page.

    When page is accessed a stored procedure is executed that will provide a list of all the RA

    (Problem -- when a new building is added, this stored procedure must be opened and syntax added)

    Results are stored in a drop down list on the page

     

     

    Thoughts for changing (reason -- will allow additional users to be able to maintain on the back-end)

    Create a table holds list of all buildings something similar to this

    Create Table [dbo].[BuildingInformation] (

    ID INT IDENTITY NOT NULL,

    BuildingName varchar(50),

    RAName varchar(50)

    )

    Create View [RA] As

    Select BuildingName, Distinct(RAName)

    FROM buildinginformation

    GROUP BY BuildingName

     

    Alter the stored procedure so that when it is run it will iterate the BuildingInformation table and compare the value in BuildingInformation to the

    values in the field BuildingName of the view [RA].  If all buildings contained in the table [BuildingInformation] exist in the view [RA] nothing will

    change.  If their are values in the [BuildingInformation] table that do not exist in the view [RA], the view [RA] will be dropped and recreated

    with a 'Union ALL' for each building in the table

    Thursday, June 25, 2015 3:00 PM

Answers

  • So what you're saying is, you have a 'VIEW' which looks like this:

    CREATE VIEW badPlan AS
    
    SELECT 'Building One' AS buildingName, 31 AS buildingID
    UNION ALL
    SELECT 'Building Two' AS buildingName, 32 AS buildingID
    UNION ALL
    SELECT 'Building Three' AS buildingName, 33 AS buildingID
    UNION ALL
    SELECT 'Building Four' AS buildingName, 34 AS buildingID
    

    To this I can only respond WTF. This is a terrible plan. Create a table, and populate it from that view right now. From then on, just use the table. When you need to add a building, just insert it into the table.

    If Joe wanders in here he's get all green and run around screaming "CELKO SMASH".


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    Thursday, June 25, 2015 4:18 PM

All replies

  • Don't really understand your question or the existing information you have.  So, instead I will focus on what seems to be the point of failure.

    Problem -- when a new building is added, this stored procedure must be opened and syntax added 

    Why is this required?  Are the values hard-coded?  If so, WHY?? Is there not a list of buildings that should be used/retrieved by this stored procedure?  And by list, I mean a table.  Given your question, I don't think a dynamically generated view is an improvement even if I understood exactly what you are proposing.  And dynamically generating objects has other issues you have not yet considered.  Perhaps foremost is that it requires a level of skill that you may not possess.

    Alter the stored procedure so that when it is run it will iterate the BuildingInformation table and compare the value in BuildingInformation to the values in the field BuildingName of the view [RA]. 

    This makes no sense.  Your view is defined as a query against the buildinginformation table.  So iterating through the BuildingInformation table and comparing <something> to the values in the view doesn't make much sense - they are effectively the same (with the exception of the rather odd use of distinct).  Nothing can exist in the view that does not already exist in the table.  I see also that your table has an identity column.  Why?  What is the actual primary key?  And why does the table have no natural key (or keys)?  If you clean up your schema, perhaps you will avoid the very issue you are trying to solve after the fact. 

    Lastly, a view here (dynamically generated or otherwise) does not seem to serve any useful purpose.  If the only thing that uses this view is the procedure, then all of this logic can be encapsulated inside the procedure. 

    Thursday, June 25, 2015 3:39 PM
  • Please try to clarify your issue

    for example: the view that you use have no meaning. There is no built-in function named Distinct. If you created your own function then please post the code.

    Please post your current DDL and post some sample data. according to the sample data data please try to clarify using specific samples.

    Thanks :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    Thursday, June 25, 2015 3:44 PM
  • UNION (ALL) joins to similar data sets together... If you have your buildings in a single table, there is no need for a union at all. Simply select from the table.

    A view does not need to be recreated when new rows are added to a table, it will simply perform the select as it always did, but will return new rows if new rows are available.

    I think maybe you need to rephrase your question, perhaps providing some example data and DDL?


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    Thursday, June 25, 2015 3:46 PM
  • Sorry for my poor explenation.  Currently their is a view that has about 30 lines of syntax PER BUILDING and each time a building is added the SAME 30 lines of code are added just changing the building name (which is hardcoded).  

    I was trying to alter this so that 1 code is less redundant and 2 easier on the user, of creating a table to house all the possible buildings.  Then maybe use Cursor or something like that to iterate over all the buildings and get a list of all the Distinct RA's to populate the drop down list.

    Does that help clarify?

    Thursday, June 25, 2015 4:10 PM
  • So what you're saying is, you have a 'VIEW' which looks like this:

    CREATE VIEW badPlan AS
    
    SELECT 'Building One' AS buildingName, 31 AS buildingID
    UNION ALL
    SELECT 'Building Two' AS buildingName, 32 AS buildingID
    UNION ALL
    SELECT 'Building Three' AS buildingName, 33 AS buildingID
    UNION ALL
    SELECT 'Building Four' AS buildingName, 34 AS buildingID
    

    To this I can only respond WTF. This is a terrible plan. Create a table, and populate it from that view right now. From then on, just use the table. When you need to add a building, just insert it into the table.

    If Joe wanders in here he's get all green and run around screaming "CELKO SMASH".


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    Thursday, June 25, 2015 4:18 PM
  • @patrick -- yes that is the current set-up.  TERRIBLE!!  

    Thursday, June 25, 2015 4:37 PM