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