title: Passing NULL value into parameterized delphi SQL server query
body: <p>I am trying to pass in a null value to a TSQLDataset parameter. The query has the form:</p>
  
  <pre><code>Query_text:='MERGE INTO [Table] 
               USING (VALUES (:A,:B)) AS Source (Source_A, Source_B)
               ....
               WHEN MATCHED THEN 
               UPDATE SET A = :A
               WHEN NOT MATCHED THEN
               INSERT(A, B) VALUES (:A,:B);
  
  SQL_dataset.CommandType:=ctQuery; 
  SQL_dataset.CommandText:=Query_text;
  
  SQL_dataset.ParamByName('A').AsString:='A';  
  SQL_dataset.ParamByName('B').AsString:={ COULD BE NULL, OR A STRING };    
  
  SQL_dataset.ExecSQL;
  </code></pre>
  
  <p>Parameter B is nullable, but is also a foreign key. If the user enters something in this field, then B must be validated against values in another table. If it is blank then I want it to be ignored. I was passing in '', but this obviously produces a FK violation error.</p>
  
  <p>I tried:</p>
  
  <pre><code>SQL_dataset.ParamByName('B').Value:=Null;
  </code></pre>
  
  <p>..but then I get a "dbexpress driver does not support the tdbxtypes.unknown data type" error.</p>
  
  <p>I also tried:</p>
  
  <pre><code>SQL_dataset.ParamByName('B').DataType:=ftVariant;
  SQL_dataset.ParamByName('B').Value:=Null;
  </code></pre>
  
  <p>..but then got "dbexpress driver does not support the tdbxtypes.variant data type" error.</p>
  
  <p>Not sure what I am doing wrong, any help would be appreciated. I am currently drawing up a parameter list based on whether the string is populated or not, and this works well; it's just a bit clunky (in my actual query) as there are quite a few parameters to validate.</p>
  
  <p>I am using Delphi XE4 and SQL server 2012.</p>
  
  <p><strong>Update:</strong></p>
  
  <p>Thanks for all the help, your suggestions were right all along, it was something else that produced that 'dbexpress driver' error. I was creating a 'flexible' parameter list in an effort to get around my problem, and this caused the exception: </p>
  
  <pre><code>Parameter_string:='';
  
  If B&lt;&gt;'' then Parameter_string:='B = :B,'
  
  Query_text:='MERGE ...'
              '...'
              'UPDATE SET A = :A, '+Parameter_string+' C = :C' ....
  </code></pre>
  
  <p>... the idea being that if B is blank then the parameter won't be 'listed' in the query.</p>
  
  <p>This doesn't work, or my implementation of it doesn't work (not sure why, I'm obviously missing a step somewhere). </p>
  
  <p>Anyway, the working code:</p>
  
  <pre><code>Query_text:='MERGE ...'
              '...'
              'UPDATE SET A = :A, B = :B, C = :C' ....
  
  SQL_dataset.CommandType:=ctQuery; 
  SQL_dataset.CommandText:=Query_text;
  
  If B&lt;&gt;'' then
  begin
    SQL_dataset.ParamByName('B').AsString:='B';
  end
  else
  begin
    SQL_dataset.ParamByName('B').DataType:=ftString;
    SQL_dataset.ParamByName('B').Value:=Null;
  end;
  </code></pre>