title: SQLCLR Stored procedures with optional parameters / default values
body: <p>Now that C# supports optional parameters, is there a way to write SQL CLR stored procedures so that publishing from Visual Studio will create the stored procedures in SQL server using optional parameters where defined in C#?</p>
  
  <p>The only way to do it in the past was by manually writing wrapper functions:<br>
  <a href="https://stackoverflow.com/questions/1068619/default-parameter-values-optional-parameters-for-net-stored-procedures-in-sql-s">Default parameter values/optional parameters for .NET stored procedures in SQL Server 2005</a></p>
  
  <p>I'd rather avoid that as it requires writing the interface twice, as well as maintaining two interfaces when things change.</p>
  
  <p>Example:  </p>
  
  <pre><code>[Microsoft.SqlServer.Server.SqlProcedure]
  public static void help(string FunctionName = "")
  {
      SqlPipe pipe = SqlContext.Pipe;
      pipe.Send("help&gt; " + FunctionName + "&lt;");
  }
  </code></pre>
  
  <p>Publishing from VS2013 yields (in the generated publish script):</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[help] @FunctionName [nvarchar](MAX)
  AS EXTERNAL NAME [YOURDB].[StoredProcedures].[help];
  </code></pre>
  
  <p>Running it:</p>
  
  <pre><code>exec help
  Procedure or function 'help' expects parameter '@FunctionName', which was not supplied.
  </code></pre>
  
  <p>However, if you manually write the procedure creation as so:</p>
  
  <pre><code>drop procedure [help]
  CREATE PROCEDURE [dbo].[help] 
  ( @FunctionName nvarchar(100) = null)
  AS EXTERNAL NAME [YOURDB].[StoredProcedures].[help];
  
  exec help 
  help&gt; &lt;
  </code></pre>
  
  <p>....it now runs as expected.</p>
  
  <p>Visual Studio has all the information it needs to publish parameters as optional now that C# supports them, so either I'm doing something wrong, or they simply haven't updated the publishing implementation to recognize optional parameters.</p>