title: Default parameter values in SQL Server 2008 stored procedure
body: <p>I feel like an idiot, but I can't make this SP default a value.... Here's how I'm declaring my parameters.</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[PCS_DocumentCacheInsert]
  (
      @sessionId varchar(200),
      @mrn varchar(50) ,
      @fromDate datetime,
      @toDate datetime,
      @aggregate varchar(50),
      @author varchar(50),
      @datePerformed dateTime,
      @docId varchar(15),
      @encounterId varchar(15),
      @facility varchar(5),
      @level char(1),
      @orderedByAuthor varchar(50),
      @resultAuthor varchar(50),
      @resultCode varchar(5),
      @resultId varchar(30),
      @resultName varchar(30),
      @status varchar(5),
      @subType varchar(10),
      @type varchar(10),
      @security varchar(3),
      @serviceGroup varchar(15),
      @witmurNum varchar(10),
      @deptId varchar(10),
      @deptText varchar(40),
      @cacheCreateTS dateTime ,
      @cacheStatus varchar(8) ='notReady',
      @cacheUpdateTS datetime
  )
  </code></pre>
  
  <p>Everything works fine with this proc except I can't get <code>notReady</code> to default for <code>@cacheStatus</code>.  Google says I'm using the correct syntax.</p>
  
  <p>here is how i call in MS</p>
  
  <pre><code>EXEC    @return_value = [dbo].[PCS_DocumentCacheInsert]
      @sessionId = N'asdfssa',
      @mrn = N'asdf',
      @fromDate = NULL,
      @toDate = NULL,
      @aggregate = NULL,
      @author = N'author',
      @datePerformed = NULL,
      @docId = N'id',
      @encounterId = NULL,
      @facility = NULL,
      @level = NULL,
      @orderedByAuthor = NULL,
      @resultAuthor = NULL,
      @resultCode = NULL,
      @resultId = NULL,
      @resultName = NULL,
      @status = NULL,
      @subType = NULL,
      @type = NULL,
      @security = NULL,
      @serviceGroup = NULL,
      @witmurNum = NULL,
      @deptId = NULL,
      @deptText = NULL,
      @cacheCreateTS = NULL,
      @cacheStatus = NULL,
      @cacheUpdateTS = NULL
  </code></pre>
  
  <p>SELECT  'Return Value' = @return_value</p>
  
  <p>GO</p>
  
  <p>so i added this and its working now, but I don't understand why when I right click and say execute stored procedure then select the null check boxes why it wouldn't default.  I guess checking null sends 'NULL' to the proc and not DBNull?</p>
  
  <pre><code>if @cacheStatus is null
  begin
      set @cacheStatus ='notReady'
  end
  </code></pre>