SQL Query Parameters

Question

This works just fine:

Query  = 'SELECT * from table_1 where code = :value; ';

I'm however trying to use the LIKE statement and It says that It couldn't find the parameter VALUE In this case:

Query = 'SELECT * from table_1 where code LIKE ''%:value;%''';

Param := ADOQuery1.Parameters.ParamByName('value');
Param.DataType := ftString;
Param.Value := 'bob';

I wanted to use a backslash to ignore the quotes, because It works in most languages but It looks like It doesn't work in Delphi.

What happens if you remove the `;` from the value? Pretty sure that's what's throwing it off. I didn't even think that was valid SQL syntax. **EDIT** Actually, I see your problem. You can't use quotes around a parameter, but at the same time, you need the `%` in there. Instead, include `%` in the actual value you pass to the parameter, not in the statement. — Jerry Dodge, May 12 '17 at 17:14
The second part (after the EDIT you made) I basically included the `%` with 'bob'. — Da black ninja, May 12 '17 at 17:19
@JerryDodge fyi `;` is the statement terminator in the ANSI SQL standard. However, in Microsoft's TSQL it's _usually_ optional. The place where I need it most often is when using the very powerful CTE's. To the point where I'm in the habit of prefixing them with `;` to ensure whatever previous statement may exist is terminated. E.g. `;WITH CteAlias AS (SELECT …) …` — Disillusioned, May 13 '17 at 05:08
@Craig Yeah, I'm used to using `GO` to do that using MSSQL tools. — Jerry Dodge, May 13 '17 at 05:36
@JerryDodge `GO` and `;` aren't equivalent. E.g. You can use `;` within a stored procedure but `GO` would be the end of the stored procedure definition (as a side-effect of ending the query batch). Also note that `GO` is actually a peculiarity of [specific client tools](http://stackoverflow.com/q/40814/224704). It's not something supported on the server. This is why query files run though SSMS don't always work when simply sent as is to the server via ADO. — Disillusioned, May 14 '17 at 08:53

score 5 · Accepted Answer · answered May 12 '17 at 17:21

Parameters automatically put the quotes around strings for you. Therefore, you cannot include such quotes in the SQL query. Because of that, you also cannot pass the % syntax with the query either.

Instead, keep your statement as the original (replacing = with LIKE), and pass the % around the actual value instead.

Query = 'SELECT * from table_1 where code LIKE :value';
Param := ADOQuery1.Parameters.ParamByName('value');
Param.DataType := ftString;
Param.Value := '%bob%';

On a side note, this code's a bit more elegant...

ADOQuery1.Parameters.ParamValues['value']:= '%bob%';

No need to tell it it's a string. It will detect that automagically.

score 1 · Answer 2 · answered May 13 '17 at 21:01

"No need to tell it it's a string (the Param.DataType) . It will detect that automagically."

In general it is true. Actually it depends of some TConnection properties, such as .ParamCreate,.ResourceOptions.AssignedValue.rvParamCreate, .ResourceOptions.AssignedValue.rvDefaultParamType=false,and defaultParamDataType . If those properties were not changed then It will detect that automagically

So in some cases that would be explicit.

SQL Query Parameters

2 Answers2