T-SQL Syntax error copying views dynamically

I'm creating a stored procedure to copy all views from one database to another using the following code.

ALTER PROCEDURE [dbo].[spCreateViews] (@SourceDB NVARCHAR(200), @DestinationDB NVARCHAR(200))
AS
BEGIN
    DECLARE @SQL2 NVARCHAR(MAX)
    SET @SourceDB = REPLACE(REPLACE(@SourceDB, '[', ''), ']','')
    SET @DestinationDB = REPLACE(REPLACE(@DestinationDB, '[', ''), ']','')

    SET @SQL2 = 
        'USE [' + @SourceDB + '] 
        BEGIN
        DECLARE @SQL NVARCHAR(MAX)
        DECLARE CUR_V CURSOR FOR
        SELECT sc.text 
        FROM sys.views av 
            JOIN sys.syscomments sc ON sc.id = av.object_id
        OPEN CUR_V
        FETCH NEXT FROM CUR_V INTO @SQL
        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC sp_executesql N''USE [' + @DestinationDB + '] EXEC sp_executesql '' + @SQL +  ''''   
            FETCH NEXT FROM CUR_V INTO @SQL
        END
        CLOSE CUR_V
        DEALLOCATE CUR_V
        END'

    EXEC sp_executesql @SQL2
END

The stored procedure is returning the error INCORRECT SYNTAX NEAR '+'

I'm receiving the error at the first + before @SQL

As far as I can tell this syntax should be correct.

Here is the output for @SQL2 to help get rid of some of the quote nesting in case that is helpful.

USE [SOURCEDBNAME]   
BEGIN  
    DECLARE @SQL NVARCHAR(MAX)  
    DECLARE CUR_V CURSOR FOR  
    SELECT sc.text     
    FROM sys.views av          
        JOIN sys.syscomments sc ON sc.id = av.object_id  
    OPEN CUR_V  
    FETCH NEXT FROM CUR_V INTO @SQL  
    WHILE @@FETCH_STATUS = 0  
    BEGIN    
        EXEC sp_executesql N'USE [DESTINATIONDBNAME] EXEC sp_executesql ' + @SQL +  ''      
        FETCH NEXT FROM CUR_V INTO @SQL  
    END  
    CLOSE CUR_V  
    DEALLOCATE CUR_V  
END

You cannot do EXEC sp_executesql on combined literals. You must use a variable as in:

ALTER PROCEDURE [dbo].[spCreateViews] (@SourceDB NVARCHAR(200), @DestinationDB NVARCHAR(200))
AS
BEGIN
    DECLARE @SQL2 NVARCHAR(MAX)
    SET @SourceDB = REPLACE(REPLACE(@SourceDB, '[', ''), ']','')
    SET @DestinationDB = REPLACE(REPLACE(@DestinationDB, '[', ''), ']','')

    SET @SQL2 = 
        '
        BEGIN
        DECLARE @SQL NVARCHAR(MAX)
        DECLARE @cmd NVARCHAR(MAX);
        DECLARE CUR_V CURSOR FOR
        SELECT sm.definition 
        FROM ' + QUOTENAME(@SourceDB) + '.sys.views av 
            JOIN ' + QUOTENAME(@SourceDB) + '.sys.sql_modules sm ON sm.object_id = av.object_id
        OPEN CUR_V
        FETCH NEXT FROM CUR_V INTO @SQL
        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC ' + QUOTENAME(@DestinationDB) + '.sys.sp_executesql @SQL;
            FETCH NEXT FROM CUR_V INTO @SQL
        END
        CLOSE CUR_V
        DEALLOCATE CUR_V
        END'

    EXEC sp_executesql @SQL2
END

This code uses @SourceDB and @DestinationDB in the dynamic SQL to correctly identify the source and destination databases. This negates the need for the USE [Database] statement on line after SET @SQL2 =.

StackExchange Database Administrators Q#24035, answer score: 3