Is an automatic update to script behind ALTER VIEW possible?
-
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?
-
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
-
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
-
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
-
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
-
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!
-
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply