title: Get inserted primary key in MS SQL before COMMIT
body: <p>In PHP I'm trying to insert multiple rows into two separate tables.  Because these all need to succeed, I'm starting with:</p>
  
  <pre><code>mssql_query("BEGIN TRAN");
  </code></pre>
  
  <p>Then I run through the insert into the main table:</p>
  
  <pre><code>$insert_Proposal = 'INSERT INTO PROPOSALS( ';
  
  $insert_Proposal .= 'ApprovedByDev, ';
  $insert_Proposal .= 'EstFundingEndFiscalYear, ';
  $insert_Proposal .= 'EstFundingStartDate, ';
  $insert_Proposal .= 'ProjectDesc, ';
  $insert_Proposal .= 'ProjectType, ';
  $insert_Proposal .= 'ProposalComments ';
  
  $insert_Proposal .= ') VALUES ( ';
  
  $insert_Proposal .= sqlSafeVars($_POST['ApprovedByDev'], "varchar") .', '; 
  $insert_Proposal .= sqlSafeVars($_POST['EstFundingEndFiscalYear'], "bigint") .', '; 
  $insert_Proposal .= sqlSafeVars($_POST['EstFundingStartDate'], "varchar") .', '; 
  $insert_Proposal .= sqlSafeVars($_POST['ProjectDesc'], "varchar") .', '; 
  $insert_Proposal .= sqlSafeVars($_POST['ProjectType'], "bigint") .', '; 
  $insert_Proposal .= sqlSafeVars($_POST['ProposalComments'], "varchar") .' '; 
  
  $insert_Proposal .= ')';
  </code></pre>
  
  <p>What I need is to get the autonumbered ProposalID that will be created by this so I can use it to insert records into the other table, before I issue the <strong>COMMIT</strong></p>
  
  <p>I've found suggestions to use something like:</p>
  
  <pre><code>output inserted.pk
  </code></pre>
  
  <p>or</p>
  
  <pre><code>INSERT INTO Persons (FirstName) VALUES ('Joe');
  SELECT ID AS LastID FROM Persons WHERE ID = @@Identity;
  </code></pre>
  
  <p>But these don't seem to work if you aren't committing the initial transaction.  Thanks in advance for any help.</p>