TADOQuery : Passing null as parameter and getting the resultset

Question

I am working on Delphi7 and SQlserver 2008 R2. i have a table with some data like below.\

CREATE TABLE dbo.tstTable (
    ID int IDENTITY(1,1) PRIMARY KEY,
    Name varchar(255) NOT NULL,
    Eid int null,
    Pid int null
);

insert into tstTable(Name,Eid,Pid) values ('name1',1,null);
insert into tstTable(Name,Eid,Pid) values ('name2',2,null);
insert into tstTable(Name,Eid,Pid) values ('name3',3,null);
insert into tstTable(Name,Eid,Pid) values ('name4',null,4);
insert into tstTable(Name,Eid,Pid) values ('name5',null,5);
insert into tstTable(Name,Eid,Pid) values ('name4',null,6);
insert into tstTable(Name,Eid,Pid) values ('name7',null,null);

now i want to get the record where Eid is equal to 1. in sql server i am able to get the result.

when i try the same in delphi i am not getting any result. in Delphi, i have taken TADOConnection, TADOQuery, TDataSource and TDBGrid.

SET TADOQuery.Query = 'select Name from tstTable where Eid=:Eid and Pid =:Pid'

  with ADOQuery1 do
  begin
    Parameters.ParamByName('Eid').Value := 1;
    Parameters.ParamByName('pid').Value := NULL;//i tried with Unassigned also
    Close;
    open;
  end; //with

when i open the AdoQuery, in grid it is not showing any records. below code is also not returning any records.

  with ADOQuery1 do
  begin
    Parameters.ParamByName('Eid').Value := NULL;
    Parameters.ParamByName('pid').Value := NULL;//i tried with Unassigned also
    Close;
    open;
  end; //with

how to handle this scenario?

Use AND Pid IS NULL. NULL is not a value. It's a state of no value. — Victoria
@Victoria : i tried that. it is failing when i want to pass all the parameters are null. when we have a record like "insert into tstTable(Name,Eid,Pid) values ('name7',null,null);" — DelphiLearner
where something = null just doesn't work. Only IS and IS NOT can return true when comparing something with null. The other operators always return false. You say it works in SQL server, but it doesn't. You're executing a different query there, where (probably) that whole condition is omitted. — GolezTrol
Could you please test that again? The query from the screenshot should not return results at all. Maybe you didn't run the full query, or the tool you used performs some magic, but that query as-is should not return anything. It's a basic rule of SQL, and normally you would need stuff like this to work around it. — GolezTrol
Maybe your SET ANSI_NULLS is set to OFF in the SSMS? You could use that to use = and <> operators with null, but I don't recommend it since in a future version, ANSI_NULLS will always be ON. use IS NULL as already suggested. — kobik

Ilyes Ilyes · Accepted Answer · 2017-10-27T12:11:34

I can say that the query you provide in the image return (0) rows if SET ANSI_NULLS is ON:

You can try to use set it to OFF:

procedure TForm1.Button1Click(Sender: TObject);
begin
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add('SET ANSI_NULLS OFF;');     
  ADOQuery1.SQL.Add('select Name from tstTable where Eid=:Eid and Pid =:Pid');
  ADOQuery1.Parameters.ParamByName('Eid').Value := 1;
  ADOQuery1.Parameters.ParamByName('pid').Value := NULL;
  ADOQuery1.Open;    
end;

Tested in: Delphi V7 (Build 4.453).

TADOQuery : Passing null as parameter and getting the resultset

2 Answers