title: Passing Null value parameter to dynamic SQL query. How can this be successfully executed?
body: <p>I am executing a dynamic SQL query. where parameters are passed dynamically. I first wrote similar to following code.</p>
  <pre><code>DECLARE @Id nvarchar(max);
  DECLARE @query nvarchar(max);
  SET @Id ='RMH_108'
  SET @query ='Select * 
               FROM [A06].[Syn_RMDemand]
               WHERE RMHierarchyId =' + @Id
  PRINT @query
  EXEC(@query)
  </code></pre>
  <blockquote>
  <p><img src="https://i.stack.imgur.com/9NZRV.png" alt="Error" /></p>
  </blockquote>
  <p>Then I wrote this.</p>
  <pre><code>DECLARE @Id nvarchar(max);
  DECLARE @query nvarchar(max);
  SET @Id ='RMH_108'
  SET @query ='Select * 
               FROM [A06].[Syn_RMDemand]
               WHERE RMHierarchyId = '''+@Id+''''
  PRINT @query
  EXEC(@query)
  </code></pre>
  <blockquote>
  <p><img src="https://i.stack.imgur.com/MSpOA.png" alt="ERROR" /></p>
  </blockquote>
  <p>This time code successfully executed. Since parameter can be empty I need to convert that to null. I modified the code and wrote this</p>
  <pre><code>DECLARE @Id nvarchar(max);
  DECLARE @query nvarchar(max);
  SET @Id = ''
  SET @Id = NULLIF(@Id,'')
  -- COMMENTED SET @Id ='RMH_108'
  
  SET @query ='Select * FROM [A06].[Syn_RMDemand]
               WHERE RMHierarchyId = '''+@Id+''''
  PRINT @query
  EXEC(@query)
  </code></pre>
  <blockquote>
  <p><img src="https://i.stack.imgur.com/i5xsL.png" alt="Error" /></p>
  </blockquote>
  <p>No errors. Query crashed.<br>
  Does anybody come up with this sort of problem ?</p>