title: T-SQL: How Do I Create A "Private" Function Inside A Stored Procedure
body: <p>Okay so I'm writing a SQL Server 2008 Stored Procedure (maintenance script).</p>
  
  <p>In doing so, being a good boy I've done plenty of error handling, checking rowcounts, printing output messages, etc</p>
  
  <p>But in doing this, I've found myself writing over and over again something like this:</p>
  
  <pre><code>SELECT @RowsAffected = @@ROWCOUNT
  IF @RowsAffected &gt; 0
  BEGIN
     PRINT CAST(@RowsAffected, NVARCHAR(2)) + 'rows updated.'
  END
  </code></pre>
  
  <p>Or debug messages like this:</p>
  
  <pre><code>PRINT 'User ' + CAST(@UserId AS NVARCHAR(5)) + ' modified successfully'
  </code></pre>
  
  <p>Is there a way I can create a kind of 'subroutine' inside the stored procedure (like a private method) that can accept something as a parameter (doesn't have to though) and do some logic?</p>
  
  <p>I want to be able to do something like this:</p>
  
  <pre><code>CheckRowCounts
  </code></pre>
  
  <p>Or this:</p>
  
  <pre><code>PrintUserUpatedMessage(@UserId)
  </code></pre>
  
  <p>Which would then perform the above logic (check rowcount, print message, etc)</p>
  
  <p>And yes obviously I can create a UDF, but then i would need to create/drop it etc as this logic is only required for the life of the execution of this stored procedure.</p>
  
  <p>Getting sick and tired of writing the same code over and over again, and changing all the different areas I've used it when I get an error =)</p>
  
  <p>Can anyone help?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>Ok so I ended up creating a scalar UDF function (seems only way).</p>
  
  <p>However, I have awarded the correct answer to Fredrik as although I don't plan to implement this, it is both a correct answer and a creative one at that.</p>
  
  <p>Thanks for all the advice/help.</p>