title: Execute a Stored Procedure Inline, similar to a UDF?
body: <p>I have a structure similar to this:</p>
  
  <pre><code>Company
  (
    CompanyID
    CompanyLastTransactionNo
  )
  
  Transaction
  (
    TransactionID
    CompanyID
    CompanyTransactionNo
    TransactionAmount
  )
  </code></pre>
  
  <p>So then I have a procedure that does something like:</p>
  
  <pre><code>INSERT INTO Transaction
  (CompanyID, CompanyTransactionNo, TransactionAmount)
  SELECT 
     CompanyID,
     fn_GetNextCompanyTrancationNo(CompanyID),
     TransactionAmount
  FROM
     LoadingTable
  
  
  CREATE FUNCTION [dbo].[fn_GetNextCompanyTrancationNo]
  (
   @CompanyID CHAR(10)
  )
  RETURNS INT
  AS
  BEGIN
      DECLARE @trxno int
             DECLARE @trxnoNew int
  
      SELECT @trxno = CompanyLastTransactionNo
      FROM dbo.Company
      WHERE ID = @CompanyID;
  
      SET @trxnoNew = @trxno + 1;
  
      UPDATE dbo.Company
      SET CompanyLastTransactionNo = @trxnoNew
      WHERE CompanyID = @CompanyID;
  
  
      RETURN @trxno
  
  END
  </code></pre>
  
  <p>When compiling I get: </p>
  
  <blockquote>
    <p>Msg 443, Level 16, State 15, Procedure fns_fn_GetNextCompanyTrancationNo, Line 19
    Invalid use of a side-effecting operator 'UPDATE' within a function.</p>
  </blockquote>
  
  <p>DUH.  Totally forgot about that.</p>
  
  <p>So here is my question, is there a way to use a Stored Proc as an inline function, similiar to this?  Maybe this is a bad approach all together?</p>
  
  <p>I'm kind of stuck with the tables I have, or I would be using a sequence to get the next number?</p>