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:
- Why is this not supported? What's the difference between running this from a sproc and running it as a standalone statement?
- Why does the workaround through
exec
ing the literal SQL string work? My understanding of theexec
statement is that it just executes the SQL in-line, is that incorrect? - (Not optimistic) Is there any better way to make a change to a view from within a stored procedure?