title: ALTER TABLE with programmatically determined constant DEFAULT value
body: <p>I am trying to add a column (MSSQL 2005) to a table (Employee) with a default constraint of a primary key of another table (Department).  Then I am going to make this column a FK to that table.  Essentially this will assign new employees to a base department based off the department name if no DepartmentID is provided.<br>
  This does not work:</p>
  
  <pre><code>DECLARE     @ErrorVar       INT
  DECLARE     @DepartmentID       INT
  
  SELECT      @DepartmentID = DepartmentID
  FROM        Department
  WHERE       RealName = 'RocketScience'
  
  ALTER TABLE     [Employee]
  ADD             [DepartmentID] INT NULL
  CONSTRAINT      [DepartmentIDOfAssociate] DEFAULT (@DepartmentIDAssociate)
  SELECT @ErrorVar = @@Error
  IF (@ErrorVar &lt;&gt; 0)
  BEGIN
      GOTO FATAL_EXIT
  END
  </code></pre>
  
  <p>The Production, Test, and Development databases have grown out of synch and the DepartmentID for the DepartmentName = ‘RocketScience’ may or may not be the same so I don’t want to just say DEFAULT (somenumber).  I keep getting “Variables are not allowed in the ALTER TABLE statement” no matter which way I attack the problem.<br>What is the correct way to do this?  I have tried nesting the select statement as well which gets “Subqueries are not allowed in this context. Only scalar expressions are allowed.”
  <br><br>In Addition, what would be really great I could populate the column values in one statement instead of doing the <br><br>{ALTER null}<br> {Update values}<br> {ALTER not null} <br><br>steps.  I read something about the WITH VALUES command but could not get it to work.
  Thanks!!!</p>