views:

113

answers:

1

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?
A: 

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.

Mark Bannister
Oh that's a good idea, that'll work quite nicely.
Joe Kearney