Variables in runtime sql

[1Derry]

I'm creating runtime SQL for a query object. Simply...
SELECT *
FROM tablename
WHERE Fieldname1 = abc
and Fieldname2 = def

passing the following hardcoded value works fine
SQL.Add(' and FieldName2 = ' + '''T7379788''');

how would I pass a variable to the sql structure - along the lines of... ?
SQL.Add(' and FieldName2 = ' + strSerialNumber);

Cheers

 

[LucieLastic]

You could do this a 3 ways.

1) Using Params[] or ParamByName. You will have to close and reopen the query with this

2) Rewrite the SQL at runtime and reopen the query.

3) Filter the dataset using the Filter property. I don't recommend this though if the query returns a large dataset.

lou

There are other more indepth ways when they're large tables, eg creating a View of a subset of the data and querying on this. Or using a stored procedure, but for these you're better to consult the relevant DB forum.

 

[svanels]

SQL.Add('and Fieldname2 = :MyVariable);
ParamByName('MyVariable').AsString := MyString;
or ParamByName('MyVariable').AsString := 'T7379788';

You have to close the query and use:
SQL.Clear, SQL.Add() and at the end Open the query S. van Els
SAvanEls@cq-link.sr

 

[1Derry]

Thanks for your replies on this. After posting I actually found some old code that also works. If you're interested...

SQL.Add(' and FieldName2 = ''' + strSerialNumber + '''')


...might try the ParamByName method though.


Thanks