Is an automatic update to script behind ALTER VIEW possible?

• Jane Sanders

  Newbie

  Points: 2

  More actions

  May 30, 2006 at 1:21 am

  #168511

  I have a sitaution where I am trying to  display an increasing number of tables in a view.

  (The tables on which the view is based can be considered "count tables" based on other larger tables that can look very different except for one thing; they all have an item number field. The "count tables" have only two fields; one item number field char (17) and a count field int where I count item number grouped by item number.)

  The goal is that anyone in the company can run an sp that will ALTER VIEW so it includes the latest "count table".

  The VIEW is based on a gross item number list which has been left outer joined with all count tables on the item number.

  That way it is possible to monitor - for each item number - how many pieces have been shipped to which customer (by an increasing number of columns).

  But how does one write SQL that ALTERs a VIEW and adds an extra left outer join when a user executes an sp (including input parameters)?

  Is it possible at all?

• Ian Yates

  SSCoach

  Points: 19738

  More actions

  May 30, 2006 at 3:40 am

  #640621

  Your proc will need to execute some dynamic SQL - this is often frowned upon for general SQL queries (too many debates - hope it doesn't fire up here!) but is the only way to do it in your case.

  Build up a string for the view's code.  Then do

  exec(@myStringForView)

  I'm not sure I follow your database design - perhaps we can all come up with a better way if we can see some sample data and tables.  You could redesign (probably not possible given it is a running system) to avoid the need to create new tables and instead store the data in one table with an extra column (which I guess would be a "table_name"/category column).

  Let us know how you go

• Ian Yates

  SSCoach

  Points: 19738

  More actions

  May 30, 2006 at 3:44 am

  #640623

  Oh, and security issues are fairly important with dynamic SQL.  Any dynamic SQL is executed in the security context of the caller of the stored proc, not the owner of the stored proc.  EG, you can have stored procs written by the database owner that can do things to table that you as a user have been denied the rights to do but you as a user can execute that proc and still manipulate the tables.

  However, any dynamic SQL within the proc will execute using your rights & privileges, not those of the stored proc owner.  So the user in SQL calling the stored proc that eventually updates the view will need the rights to alter that view.

  Finally... Another way to perhaps avoid the need to recode the view would be to have an (possibly ugly) cursor adding rows to a temp table.  The cursor would loop over the tables to which you plan to join and retrieve the appropriate rows.  The rows are then added to the temp table.  The resultset would be different - rather than

  Col1, Col2, Col3, Col4

  Val1, Val2, Val3, Val4 

  you would end up with

  Name    Value

  Col1      Val1

  Col2      Val2

  Col3      Val3

  ....

  This is probably a better way to store the data in the first place (avoids changing tables & views) - but, as always, depends on your requirements

• SQLZ

  SSChampion

  Points: 12872

  More actions

  May 30, 2006 at 3:53 am

  #640626

  Hi Jane,

  I'm not sure I've completely understood your problem so correct me if I'm wrong.

  You've got a view that looks something like this.

  select

  from a1

  join a2 on a2. = a1.

  join .... and so on.

  And what you want to do is dynamically manage the fact that more and more count tables are appearing?

  If I've understood this correctly then this is what I would do. Rather than let users run an sp which alters the above view to include another table in the join statement I would dynamically build the statement inside the view. This only works if the table that a new "count table" is joining on is always the same.

  You can run this statement:

  select distinct 'join ' + table_name + ' on ' + table_name + '.itemNumber =base_table.itemNumber'

  from information_schema.columns c

  where exists

  (select * from information_schema.columns

  where table_name = c.table_name

  and (column_name = 'itemNumber'))

  and exists

  (select * from information_schema.columns

  where table_name = c.table_name

  and (column_name = 'itemCount'))

  The above statement will effectively generate your join statements between each "count table". "count table" has been identified as a table containing the columns itemNumber and itemCount but you might need to tightend that definition. baseTable is the base table against which you'll be joining.

  So what you can do is cursor through the above table, appending each join statement to the next, seperating them with char(10)+char(13).

  And once you're done, you'll need to append that to the initial part of the select statement.

  Essentially you'll end up with something like this. That should be the general idea anyway.

  Hope it helps,

  declare @select_statement varchar(8000)

  declare @join_statement varchar(500)

  set @select_statement = 'select fields,... from baseTable' + char(10) + char(13)

  declare my_cursor cursor

  for

  select distinct 'join ' + table_name + ' on ' + table_name + '.itemNumber =base_table.itemNumber'

  from information_schema.columns c

  where exists

  (select * from information_schema.columns

  where table_name = c.table_name

  and (column_name = 'itemNumber'))

  and exists

  (select * from information_schema.columns

  where table_name = c.table_name

  and (column_name = 'itemCount'))

  open my_cursor

  fetch next from my_cursor into @join_statement

  while @@fetch_status = 0

  begin

  set @select_statement = @select_statement + @join_statement + char(10) + char(13)

  fetch next from my_cursor into @join_statement

  end

  close my_cursor

  deallocate my_cursor

  sp_executesql @select_statement

  Karl
  source control for SQL Server

• SQLZ

  SSChampion

  Points: 12872

  More actions

  May 30, 2006 at 3:55 am

  #640627

  Oh, and Ian is right about the security issues behind executing dynamic sql but it is the only way to do it in this case.

  Unless you decide to change your design as Ian suggested.

  Good luck

  Karl
  source control for SQL Server

• Jane Sanders

  Newbie

  Points: 2

  More actions

  May 30, 2006 at 4:54 am

  #640637

  Thanks a lot for your input, you make the SQL world even more fun!

  I'll try, however since I'm a beginner it might take more than 5 minutes, which is also a reason why your advise to redesign is a popular option right now .

  Thanks again!

   

• Marshall Smith

  SSCrazy

  Points: 2041

  More actions

  May 30, 2006 at 7:43 am

  #640677

  If redesign is an option, I would strongly recommend it. This sort of information should be drawn from an Orders table with a GROUP BY query, not stored in an increasing number of tables.

  Don't store what you can calculate.