title: Using variable in sql postdeployment build script?
body: <p>What I would like to be able to do, is to be able to <strong>create a publising xml script in Visual Studio database project that defines a variable, that will be used to compose a script</strong>.</p>
  
  <p>My problem is that I get a Error:  "SQL72008: Variable DeployType is not defined." if I don´t define the variable in the post deployment script like this ":setvar DeployType "varchar(100)"</p>
  
  <p>When I run the publish.xml the DeployType parameter is rightly set at the top of the generated script <strong>BUT</strong> that value get overridden with the ":setvar DeployType "varchar(100)". So to make this work I would need to manually remove that line before running this script.</p>
  
  <p>So the question is <strong>How can I let the project build and be able to publish without defaulting the setvar variable in the postdeployment script?</strong></p>
  
  <p><img src="https://i.stack.imgur.com/5Pgqk.png" alt="enter image description here"></p>
  
  <p>This is the content of my PostDeployment.sql file <strong>that does not build without defaulting the DeployType variable</strong> </p>
  
  <pre><code>--The line causing my problems. I would like to skip it somehow.
  :setvar DeployType "varchar(100)"
  
  Print 'Always include core'
  :r ..\Data\Core\_BaseCore.sql
  
  --Conditionaly add based on DeployType comming from the publishing.xml
  IF ('$(DeployType)' = 'A')
  BEGIN
  :r ..\Data\A\_BaseKnap.sql
  END
  ELSE IF ('$(DeployType)' = 'B')
  BEGIN
  :r ..\Data\B\_BaseB.sql
  END
  </code></pre>
  
  <p>This is the content in the Database.publish.xml file</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003"&gt;
   &lt;PropertyGroup&gt;
     &lt;IncludeCompositeObjects&gt;True&lt;/IncludeCompositeObjects&gt;
     &lt;TargetDatabaseName&gt;db name&lt;/TargetDatabaseName&gt;
     &lt;DeployScriptFileName&gt;Database.sql&lt;/DeployScriptFileName&gt;
     &lt;ProfileVersionNumber&gt;1&lt;/ProfileVersionNumber&gt;
     &lt;TargetConnectionString&gt;Connection string&lt;/TargetConnectionString&gt;
   &lt;/PropertyGroup&gt;
   &lt;ItemGroup&gt;
     &lt;SqlCmdVariable Include="DeployType"&gt;
       &lt;Value&gt;B&lt;/Value&gt;
     &lt;/SqlCmdVariable&gt;
   &lt;/ItemGroup&gt;
  &lt;/Project&gt;
  </code></pre>
  
  <p>When I publish this to a script file <strong>this is the generated script</strong> to be run against the database I get (lots of stuff removed not needed).</p>
  
  <pre><code>:setvar DeployType "B"
  
  --This nulls out the DeployType set before
  :setvar DeployType "varchar(100)"
  
  Print 'Always include core'
  :r ..\Data\Core\_BaseCore.sql
  
  --Conditionaly add based on DeployType comming from the publishing.xml
  IF ('$(DeployType)' = 'A')
  BEGIN
  :r ..\Data\A\_BaseKnap.sql
  END
  ELSE IF ('$(DeployType)' = 'B')
  BEGIN
  :r ..\Data\B\_BaseB.sql
  END
  </code></pre>
  
  <p>And running this (in e.g Sql Manager) the result is</p>
  
  <pre><code>Print 'Always include core'
  </code></pre>
  
  <p>where it doesn't go into the IF statements because it has been defaulted.</p>
  
  <p>Hope this is clear enough.</p>