title: SELECT returns NULL if run from application
body: <p>I have an INSERT trigger on a view created with VIEW_METADATA.
  There's the following snippet:</p>
  
  <pre><code>    if @has_folder is null set @has_folder=(select REPLACE(REPLACE(REPLACE(COLUMN_DEFAULT, '(', ''), ')', ''), '''', '') from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'people' and COLUMN_NAME = 'has_folder')
  </code></pre>
  
  <p>The whole point of this subquery is to set <code>@has_folder</code> to default value for a column if it is NULL. And to avoid editing trigger text if default changes. As the default value is stored in DB surrounded by double parentheses (<code>'((0))'</code>), I remove them.</p>
  
  <p>It does work when I run an INSERT from SQL Server Management Studio, but it does not work when the same query is run from application (<code>@has_folder</code> stays NULL). The query is the same, I've checked with SQL Profiler.</p>
  
  <p>If I set the statement to</p>
  
  <pre><code>    if @has_folder is null set @has_folder=0
  </code></pre>
  
  <p>it works, but then I'll have to change it manually if I decide to change the default value for that column in database.</p>
  
  <p>In general,</p>
  
  <pre><code>select COLUMN_DEFAULT from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'people' and COLUMN_NAME = 'has_folder'
  </code></pre>
  
  <p>returns correct value if run from SSMS, but NULL if run from application.</p>
  
  <p>The same behavior is shown by</p>
  
  <pre><code>select definition from sys.columns 
  join sys.tables on sys.columns.object_id = sys.tables.object_id
  join sys.default_constraints on sys.default_constraints.object_id = default_object_id
  where sys.columns.name = 'has_folder'
  and sys.tables.name = 'people'
  </code></pre>
  
  <p>What is going on and how can it be fixed?</p>