Create A View With Dynamic Sql
Instead of double nesting, another approach is to create a stored procedure whose only purpose is to executes dynamic SQL
CREATE PROCEDURE [dbo].[util_CreateViewWithDynamicSQL]
@sql nvarchar(max)
AS
BEGIN
SET NOCOUNT ON;
EXECUTE (@sql)
END
The stored procedure above can be re-used. Anytime you need to create a view just call the stored procedure and pass it the dynamic sql.
EXECUTE util_CreateViewWithDynamicSQL 'create view Test as select * from sys.databases'
I prefer this approach because dynamic sql is confusing enough and adding double nesting complicates it further.
You can do this by double nesting the dynamic SQL statements then:
begin tran
declare @sql nvarchar(max) =
N'use [AdventureWorks2012];
exec (''create view Test as select * from sys.databases'')';
exec (@sql);
select * from AdventureWorks2012.sys.views
where name = 'Test'
rollback tran