Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Dec 2002
    Posts
    50

    Unanswered: Procedurally generating a large View

    I have a procedure that creates a large dynamic view of several tables. The view is a union view of up to 15 tables. The table names are all <name>_DDMM where name is the standard table name and ddmm is the day and month of the tables data. The tables are created by a software supplied by another company, so I can not ensure that the tables will always have exactly the same fields or number of fields. Sometimes the company will add more fields to the tables in thier updates. So, I have to include the field names in the SQL exec command to create the query. This makes for a very long exec command and depending on the number of tables it needs to include, it can require upwards of a 16,000 character string. Obviously, this can't work, so I had to break up the variable in order to create the procedure. However, I'm wondering if there isn't a better method than creating three different 8000 varchar variables and having overflow write to the next variable in line. Especially if the number of tables needs to be expanded, it could be a problem. Is there a better way to run a create view exec command on a large number of characters?

    EDIT: Changed the title to read Procedurally generating a large view.
    Last edited by Ishe; 10-11-07 at 19:51.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    you have a stored procedure that creates a view with dynamic sql? seems like a bad idea. stored procedures are for DML, not DDL.

    why not just store the view definition as a script in source control and execute it as necessary? when your supplier adds columns to their tables, you just add those columns to your script and execute it again.

  3. #3
    Join Date
    Dec 2002
    Posts
    50
    I don't really know what you mean by storing it as a script in a source control. By a source control, do you mean a third party utility? I am not familiar with the term. I don't really have any third party utilities or compilers to work with, just SQL Server 2000. The stored procedure that I have has been working fine. I just wanted to find out if there is a more efficient way to do the same thing. Basically, I need the view to look at different tables every day. The tables are indicated at the end of the table name by day and month of the data they contain. The basic outline of the stored procedure I have is below.

    Create Procedure ProcName
    as
    Declare @DatabaseName as varchar (128)
    Declare @sql as varchar(8000)
    Declare @view_Name as varchar (128)
    Declare @table_Name as varchar (128)
    Declare @ProcDate as datetime
    Declare @cntr as Int
    Declare @sql2 as varchar(8000)
    Declare @sql3 as varchar(8000)


    Select @DatabaseName = DB_NAME()
    exec usp_DayToProcess Null, @ProcDate output

    Set @cntr = 0
    Set @view_Name = 'ViewName'
    Set @Path2 = ''
    Set @Path3 = ''
    Set @Path = 'CREATE VIEW ViewName AS SELECT * FROM ('

    While (@cntr < 15)
    Begin
    Set @table_Name = '[TableName_' + SubString(Convert(VarChar(10), DateAdd(day, -(@cntr), @ProcDate), 101), 1, 2) + SubString(Convert(VarChar(10), DateAdd(day, -(@cntr), @ProcDate), 101), 4, 2) + ']'
    if exists (select * from dbo.sysobjects where id = object_id(@table_Name) and OBJECTPROPERTY(id, N'IsUserTable') = 1 and crdate > DateAdd(year, -1, @ProcDate))
    Begin
    If @cntr <> 0
    Begin
    Set @Path = @Path + 'UNION ALL '
    End
    Set @Path = @Path + 'SELECT ... FROM ' + @table_Name
    End
    Set @cntr = @cntr + 1
    If Len(@Path) > 7000
    Begin
    If Len(@Path2) > 7000
    Begin
    Set @Path3 = @Path2
    End
    Set @Path2 = @Path
    Set @Path = ''
    End
    End

    Set @Path = @Path + ') TempView ORDER BY ...'

    EXECUTE (@Path3 + @Path2 + @Path)
    GO

    Since I have several fields that have to be reformated from the tables as well as functions to perform on some of the fields in order to get the values I need, the sql gets fairly large. So, it ends up taking more than two varchar variables to store all of the sql to search 15 tables. I am trying to standardize the procedure a bit, so in case more than 15 days of tables are required, it would require more variables. I was wondering if there is a more efficient way of doing this with SQL Server 2000 alone.

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    source control is part of how professionals write code. it allows you to see how the code has changed in time.

    http://en.wikipedia.org/wiki/Revision_control

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What is the point of this:
    Code:
    Set @Path = @Path + 'SELECT ... FROM ' + @table_Name
    Are you manually coding the column names?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Personally, I like the SELECT * part
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Ishe
    The tables are indicated at the end of the table name by day and month of the data they contain.

    That is just so wrong on so many levels
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Dec 2002
    Posts
    50
    Quote Originally Posted by jezemine
    source control is part of how professionals write code. it allows you to see how the code has changed in time.

    http://en.wikipedia.org/wiki/Revision_control
    So, you are basically saying to modify the code each day/week/whenever it needs to be run?

    Quote Originally Posted by Blindman
    What is the point of this:

    Code:
    Set @Path = @Path + 'SELECT ... FROM ' + @table_NameAre you manually coding the column names?
    The '...' is where I am specifying the fields to use. I didn't include all of it, because it is a bit long. For instance, I am adding a date field into the view so that the date of the transaction is a field. The tables do not have a transaction date field, since they are a different table for each day. Also, I specify the field names, because there are times that the company who creates the code that makes the tables will change that code during an update. I could check the table for any changes each time they put out updates, but this aggregates several tables. So, some of the tables would be missing fields that others have within the tables that are being aggregated. This would cause an error if the fields to use were not specified.


    Quote Originally Posted by Brett Kaiser
    Personally, I like the SELECT * part
    After the tables are aggregated in the view, they are wrapped with a SELECT * in order to put them in some semblence of order. I order them by the primary key, then by date with the SELECT *.

    Quote Originally Posted by Brett Kaiser
    Quote Originally Posted by Ishe
    Originally Posted by Ishe
    The tables are indicated at the end of the table name by day and month of the data they contain.
    That is just so wrong on so many levels
    I know what you mean, but I didn't really design the tables or the code to make the tables. It's just the only thing I have to work with really.

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by Ishe
    So, you are basically saying to modify the code each day/week/whenever it needs to be run?
    yes, that's what I would do. There is great value in knowing what the definition of the view was at a certain time.

    Also I don't like the idea of generating permanent database objects from a proc. If you do that, you are building on a very shaky foundation.

    To me it's the same thing as writing self modifying code in a compiled app, for example by coding with Reflection.Emit() in C#. hard to debug, hard to know what the actual state of the system was at any given time.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Ishe
    After the tables are aggregated in the view, they are wrapped with a SELECT * in order to put them in some semblence of order. I order them by the primary key, then by date with the SELECT *.
    SELET * has NOTHING to do with the ordering off a resultset.
    For that you need an ORDER BY clause.
    George
    Home | Blog

  11. #11
    Join Date
    Dec 2002
    Posts
    50
    Quote Originally Posted by georgev
    SELET * has NOTHING to do with the ordering off a resultset.
    For that you need an ORDER BY clause.
    I know, and it has an ORDER BY clause at the end, but I have found from experience with prior UNION views that if you slap an ORDER BY clause at the end of the last union, it doesn't order the entire result set, just the last SELECT. So, I wrapped the entire UNION query making the UNION query a subquery and put the ORDER BY clause at the end of the wrapping query.

    Quote Originally Posted by jezemine
    Quote Originally Posted by Ishe
    Originally Posted by Ishe
    So, you are basically saying to modify the code each day/week/whenever it needs to be run?
    yes, that's what I would do. There is great value in knowing what the definition of the view was at a certain time.

    Also I don't like the idea of generating permanent database objects from a proc. If you do that, you are building on a very shaky foundation.

    To me it's the same thing as writing self modifying code in a compiled app, for example by coding with Reflection.Emit() in C#. hard to debug, hard to know what the actual state of the system was at any given time.
    Does that include creating tables through stored procedure?

    In this case I am trying to create this in such a way that it won't take someone that knows anything much about SQL Server to be able to use the procedure. Since the tables that the user would need to use change on a daily basis, I don't know of any other way to accomplish this. I can't rewrite the code for them every day. I am actually trying to change the code to be less customized, not more so.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Ishe
    The '...' is where I am specifying the fields to use.
    If you have to manually code these anyway, what is the point of the sproc? I mean, if your code grabbed the columns names from the schema and automagically built the view, that would be one thing, but I'm having trouble understanding the overall purpose of this process.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Dec 2002
    Posts
    50
    Quote Originally Posted by blindman
    If you have to manually code these anyway, what is the point of the sproc? I mean, if your code grabbed the columns names from the schema and automagically built the view, that would be one thing, but I'm having trouble understanding the overall purpose of this process.
    The only reason that it is done in a stored procedure is because the tables that I have to work with are daily transaction tables. The table names indicate the day and month of the transactions that are contained within. I don't think I can create a standard view with daily changing table names.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    But you don't have standard columns!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Dec 2002
    Posts
    50
    Quote Originally Posted by blindman
    But you don't have standard columns!
    I'm not sure what you mean. The below contains the code that I replaced '...' with in the example.

    Set @Path = @Path + 'SELECT FB_MBRNO AS [Member No], Convert(datetime, ' + CHAR(39) + Convert(VarChar(10), DateAdd(day, -(@cntr), @ProcDate), 102) + CHAR(39) + ') AS [Tran Date], FB_TLR AS Tlr, FR_TRAN_CODE AS [Tran Code], FB_STATUS AS [Status], FB_CNV_CASH AS CnvCash, FR_TIM AS [Time], FB_CASH AS [Cash], FB_CHECK AS [Checks], FB_APPLIED AS Applied, FB_CASHBACK AS [Cash Back], FB_REVERSED AS Reversed, FB_MISC_CODE As [Misc Code], FB_TRAN_NO As [Tran No] FROM ' + @table_Name

    I think the columns are fairly standard, except the [Tran Date] field, which is there because the tables I am looking at do not have a date field that indicates when the transaction occured.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •