parameter is something or null

[filipe29]

hi i have the sql query :

select * from cliente
where nome like nome or nome is null

pnome is a parameter and when i go to the parameters strings i have 2 parameters not one,why??

 

[lespaul]

don't you want it like this:

select * from cliente
where nome like :pnome or nome is null

with your exising query you're going to get, (assume parameter is LESPAUL):

SELECT * FROM cliente WHERE nome LIKE LesPaul or [b]LesPaul[/b] is null

you are also going to need to add the LIKE delimiter for your Database, either * (access JET SQL) or % (other SQL flavors)

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[filipe29]

No what i want to do is to use sometimes the parameter and sometimes not.

 

[lespaul]

how do you know if the user has entered a parameter? Do you have a Edit box on a form?

If EditBox.Text <> '' then
  SQL.ADD('SELECT * FROM Cliente WHERE nome LIKE ' + QuotedStr('%' + EditBox.Text + '%'))
else
  SQL.Add('SELECT * FROM Cliente');
SQL.Active := True;

Either way you'll need to add the LIKE delimiter. Have you tried this and leave the parameter blank? What does that return?

select * from cliente
where nome like :pnome

Leslie

 

[filipe29]

i have a combo that selects the field and a textedit that set the text to find.In the combo i have fields of type string and integer and want to do a partial search.

So i need more than one parameter and when a field is selected by the combobox the others are set to null.

But for some reason when i set in the sql strings that the same parameter can be null it shows mw on the parameters two.

 

[towerbase]

Parameters can not be used for column names. They can only be used for the text as you put it.

One way to solve this is to create the SQL statement each time. For example:

...
  SQLCommand := 'SELECT * FROM cliente ' +
                'WHERE nome LIKE ' + QuotedStr(Edit1.Text) +
                ' OR ' + ComboBox1.Text + ' IS NULL';
...

Andrew
Hampshire, UK

 

[filipe29]

Once more i dont want to check if the combotext is null but the parameter.And the parameter is not the column name.

 

[lespaul]

again, don't use parameters, build your SQL each time.