Use cases for sp_executeSQL
A number of SQL commands can only be executed under the context of the current database, such as CREATE VIEW and CREATE SCHEMA. If supported by the version of SQL Server, then by using sp_executeSQL it is possible to create database objects in another database than the current database.
12/12/20231 min read
-----------------------------------------------------------------------------------------------------------------------------------
-- Declare Variables
-----------------------------------------------------------------------------------------------------------------------------------
DECLARE @cQuote char(1) = '''' --> Solves the problem of a quote in a concatenated string
DECLARE
@nvcDatabaseName nvarchar(128) = 'AdventureWorks'
, @nvcSchemaName nvarchar(128) = 'Sales'
, @nvcTableName nvarchar(128) = 'Customer'
, @nvcViewName nvarchar(100) = 'CustomerView'
DECLARE @nvcFullTableName nvarchar(386) = @nvcDatabaseName + '.' + @nvcSchemaName + '.' + @nvcTableName
-----------------------------------------------------------------------------------------------------------------------------------
-- Dynamic SQL
-----------------------------------------------------------------------------------------------------------------------------------
DECLARE @nvcDropViewSql nvarchar(max) --> The SQLstring to Drop the View
DECLARE @nvcCreateViewSql nvarchar(max) --> The SQLstring to Create the View
-----------------------------------------------------------------------------------------------------------------------------------
-- 1.1 Compose the DROP VIEW SQL - First Method
----------------------------------------------------------------------------------------
SET @nvcDropViewSql = ''
SET @nvcDropViewSql += 'IF (select count(*) from ' + @nvcDatabaseName + '.sys.views t1 inner join ' + @nvcDatabaseName + '.sys.schemas t2 on t2.schema_id=t1.schema_id where t1.name=' + @cQuote + @nvcViewName + @cQuote + ' and t2.name=' + @cQuote + @nvcSchemaName + @cQuote + ') = 1' + char(13)
SET @nvcDropViewSql += 'BEGIN' + char(13)
SET @nvcDropViewSql += ' USE ' + @nvcDatabaseName + '; exec sp_executesql N' + @cQuote + 'DROP VIEW ' + @nvcSchemaName + '.' + @nvcViewName + @cQuote + char(13)
SET @nvcDropViewSql += 'END' + char(13)
EXEC(@nvcDropViewSql)
-----------------------------------------------------------------------------------------------------------------------------------
-- 1.2 Compose the CREATE VIEW SQL - Second Method
-----------------------------------------------------------------------------------------------------------------------------------
SET @nvcCreateViewSql = ''
SET @nvcCreateViewSql += 'CREATE VIEW ' + @nvcSchemaName + '.' + @nvcViewName + ' AS' + char(13)
SET @nvcCreateViewSql += 'SELECT *' + char(13)
SET @nvcCreateViewSql += 'FROM ' + @nvcFullTableName + char(13)
SET @nvcCreateViewSql += 'WHERE [TerritoryID]=4' + char(13)
DECLARE @nvcSP_ExecuteSQL nvarchar(max) = @nvcDatabaseName + '.sys.sp_executesql'
EXEC @nvcSP_ExecuteSQL @nvcCreateViewSql
-----------------------------------------------------------------------------------------------------------------------------------
-- End of Exercise
-----------------------------------------------------------------------------------------------------------------------------------