SQL Server数据库项目部署前和部署后脚本

Question

我已经在后期部署脚本中使用查询来初始化表中添加了一列。不幸的是，我似乎无法编写可以每次运行的查询，所以我正在寻找一种方法来检查预部署脚本，如果该列可用并将参数或变量传递给部署后脚本然后将运行一次初始化查询。

尝试1：我尝试设置在预先部署脚本sqlcmd变种，但下面的语法是不允许的：

IF COL_LENGTH('dbo.Table','NewColumn') IS NULL 
    :setvar PerformInitQuery 1 

尝试2：我也使用正常的尝试变量在部署前脚本：

DECLARE @PerformInitQuery BIT = 0 
IF COL_LENGTH('dbo.Table','NewColumn') IS NULL 
    SET @PerformInitQuery = 1 

和访问它在部署后脚本：

IF @PerformInitQuery = 1 
BEGIN 
    :r ".\DeploymentScripts\PerformInitQuery.sql" 
END 

最后一次尝试似乎在从Visual Studio发布项目但不在构建服务器上工作;它使用SqlPackage.exe将生成的.dacpac文件发布到数据库。

错误SQL72014：.net SqlClient数据提供：

消息137，级别15，状态2，行12
必须声明标量变量 “@PerformInitQuery”

Answer 1

你可以试试使用临时表来保存您希望从pre传递到post脚本的值;

/* 
    Pre-Deployment Script Template       
    -------------------------------------------------------------------------------------- 
    This file contains SQL statements that will be executed before the build script. 
    Use SQLCMD syntax to include a file in the pre-deployment script.   
    Example:  :r .\myfile.sql        
    Use SQLCMD syntax to reference a variable in the pre-deployment script.   
    Example:  :setvar TableName MyTable        
        SELECT * FROM [$(TableName)]     
    -------------------------------------------------------------------------------------- 
    */ 

    select 'hello world' as [Col] into #temptable 

拾取后部署脚本;

/* 
Post-Deployment Script Template       
-------------------------------------------------------------------------------------- 
This file contains SQL statements that will be appended to the build script.  
Use SQLCMD syntax to include a file in the post-deployment script.   
Example:  :r .\myfile.sql        
Use SQLCMD syntax to reference a variable in the post-deployment script.  
Example:  :setvar TableName MyTable        
       SELECT * FROM [$(TableName)]     
-------------------------------------------------------------------------------------- 
*/ 

declare @var nvarchar(200) 
select @var = [Col] from #temptable 

print @var 

的hello world

更新完成。