6

I'm using MS SQL Server, and I'd like to alter a view from within a stored procedure, by executing something like "alter view VIEWNAME as ([some sql])".

A few pages thrown up by google assert that this is not supported directly (and neither are related alter-table statements), but there are also examples of how to work around it using constructions like this:

declare @sql varchar(max)
select @sql = 'alter view VIEWNAME as ([some sql])'
exec(@sql)

Writing code as literal strings smells a bit, even for SQL.

My questions:

  1. Why is this not supported? What's the difference between running this from a sproc and running it as a standalone statement?
  2. Why does the workaround through execing the literal SQL string work? My understanding of the exec statement is that it just executes the SQL in-line, is that incorrect?
  3. (Not optimistic) Is there any better way to make a change to a view from within a stored procedure?
Joe Kearney
  • 7,397
  • 6
  • 34
  • 45
  • `ALTER VIEW` has to be the only statement in the batch. I guess this is just for ease of parsing. When you run `EXEC` it runs as a different batch. – Martin Smith Sep 15 '10 at 10:05
  • "Ease of parsing" seems a bit of a cop out for a supposedly enterprise-ready database suite! :) Unless there's some reason it's not possible to parse? Seems unlikely.. – Joe Kearney Sep 15 '10 at 10:23
  • Why would you want to change a view in an SP? (rather than break your processing up into several stages or have more than one view for example) – MLT Sep 15 '10 at 10:36
  • We don't want to give arbitrary schema update permissions to the process that executes this. The view here is used as a layer of indirection pointing to two backing tables, we update the "other" one and then swing the view when complete. The whole thing is transactional. – Joe Kearney Sep 15 '10 at 11:23
  • @jjk: but why use a view, when you could achieve the same thing with a query? Also, when you say you "swing" the view, do you mean that you select from it? –  Sep 15 '10 at 14:19
  • By "swing" I mean `alter view as (select * from targetTable)` for the new target table, where the target alternates between `table1` and `table2`. I want to use a view so that these updates are transparent to the client, they shouldn't have to figure out which `tableX` to query before getting the data. (Is that what you mean?) – Joe Kearney Sep 15 '10 at 14:34
  • @jik, sounds like what you really need is a dynamic sql statement to select from one of the two different tables. oh, and select * is the devil. – DForck42 Sep 15 '10 at 14:48
  • @DForck: do you mean doing that in the client query, so that each query to this table must first query to decide which table is currently live before doing what they want to do? That's exactly what this is trying to avoid. This is only intended to be an optimisation that prevents locking the production table for the minutes it needs to update it, and it works fine except for this particular bit of brutality! – Joe Kearney Sep 15 '10 at 15:09
  • @jjk. So, what you're ultimately trying to do is force everything that accesses that view to look at a copy of the production data while the production data is being updated by a process? – DForck42 Sep 15 '10 at 16:32
  • Exactly. Clients only see the old version until the view is "swung" to point to the new version, so clients don't block while updates happen. But my question is more a technical one about the relation between `alter view`, sprocs and batches than about how we might achieve this if we redesigned from scratch. – Joe Kearney Sep 16 '10 at 08:08
  • This is an interesting follow-up to the question here of swapping tables: http://stackoverflow.com/questions/605774/swapping-ms-sql-tables – mg1075 Oct 25 '12 at 22:22

1 Answers1

2

I think the answers are:

  1. MS want to prevent DDL being run from within procedures.
  2. The code within the exec statement is not treated as part of the procedure - so it is not subject to the same restrictions as the procedure.
  3. No.

An alternative approach might be to have a separate table (called something like swing_table) with either 1 or 0 records to indicate whether the view should query the production or other (backup?) table respectively - something like:

create view viewname as
select {field list}
from production_table
cross join swing_table
union all
select {field list}
from backup_table
where (select count(*) from swing_table) = 0

- then TRUNCATE swing_table within the procedure when you want to, erm, swing the table - since TRUNCATE is not a transactional command, it should execute immediately.