This example isn't so original.  In fact, its taken from two sources:

Building a template to generate stored procedures
http://community.codesmithtools.com/blogs/tutorials/archive/2006/02/15/StoredProcedures.aspx

and

Video Tutorial
http://community.codesmithtools.com/blogs/videotutorials/archive/2006/02/16/StoredProcedures.aspx

The example above had some syntax error.  It is still an excellent article.  Here is a working version:

<%@ CodeTemplate Language="C#" TargetLanguage="T-SQL" Src="" 
    Inherits="" Debug="False" Description="UPDATE stored procedures." %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" 
    Category="Context" Description="Table the stored proc will be based on" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>

-- Date Created: <%= DateTime.Now.ToLongDateString() %>

CREATE PROCEDURE dbo.Update<%= SourceTable.Name %>
<% for (int i = 0; i < SourceTable.Columns.Count; i++) 
   { %>
      <%= GetSqlParameterStatement(SourceTable.Columns[i]) %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %>
<% } %>
AS

UPDATE <%= SourceTable.Name %> SET
<% for (int i = 0; i < SourceTable.Columns.Count; i++) 
   { %>
      [<%= SourceTable.Columns[i].Name %>] = @[<%= SourceTable.Columns[i].Name %>]<% if (i < SourceTable.Columns.Count - 1) { %>,<% } %>
<% } %>
   WHERE
     <% for (int i = 0; i < SourceTable.PrimaryKey.MemberColumns.Count; i++) { %>
     <% if (i > 0) { %>AND <% } %>
     [<%= SourceTable.PrimaryKey.MemberColumns[i].Name %>] = @<%= SourceTable.PrimaryKey.MemberColumns[i].Name %>
     <% } %>
    
<script runat="template">
public string GetSqlParameterStatement(ColumnSchema column)
{
  string param = "@" + column.Name + " " + column.NativeType;

  switch (column.DataType)
  {
    case DbType.Decimal:
    {
      param += "(" + column.Precision + ", " + column.Scale + ")";
      break;
    }
    default:
    {
      if (column.Size > 0)
      {
        param += "(" + column.Size + ")";
      }
      break;
    }
  }
  return param;
}
</script>

Some Notes:

- We specify input parameters to the template by using the <Property> tag.  In this example, we are using the SchemaExplorer.TableSchema as the input.  With this, we would need to enter a table for it to connect to.

The SchemaExplorer.TableSchema object has a lot of properties you can use to build the output. Included are Name (name of the table), PrimaryKey (a collection of columns that makes up the primary key) and Columns (a collection of columns for the table).

The next article would show how to automate generation by using a .NET project similar to how Subsonic (subcommander) does.