Devart Forums

Posted: **Fri 05 Feb 2010 03:39**

Hello, I have a problem with TMSQuery.Prepare in SELECT ... WHERE Col IN (SELECT ...)

Example:

CREATE TABLE Test (
    ID   int NOT NULL PRIMARY KEY,
    ID2  int,
	Data image not null
)

Code: Select all

Query.SQL.Text := 'SELECT * FROM Test WHERE ID=:ID AND ID IN (SELECT ID2 FROM Test WHERE ID2=:ID)';
Query.Params.CreateParam(ftString, 'ID', ptInput).AsString := '11112121';
Query.Prepare;

// Wrong prepare sql
// Profiler log
declare @p1 int
set @p1=1
exec sp_prepare @p1 output,N'@P1 sql_variant OUTPUT,@P2 sql_variant OUTPUT',N'SELECT * FROM Test WHERE ID=@P1 AND ID IN (SELECT ID2 FROM Test WHERE ID2=@P2)',1
select @p1

Code: Select all

      
Query.Execute;

//ERROR
---------------------------
Debugger Exception Notification
---------------------------
Project Project1.exe raised exception class EOLEDBError with message 'Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Parameter[2] :ID - invalid ParamType (Status = 1h).'. Process stopped. Use Step or Run to continue.
---------------------------
OK Help
---------------------------

Please how to solve the setup parameters to SQL query with nested SELECT.

Thank you for your reply.

Posted: **Fri 05 Feb 2010 08:31**

To solve the problem you should use the following code:

Code: Select all

Query.SQL.Text := 'SELECT * FROM Test WHERE ID=:ID AND ID IN (SELECT ID2 FROM Test WHERE ID2=:ID2)'; 
Query.Params.CreateParam(ftString, 'ID', ptInput).AsString := '11112121'; 
Query.Params.CreateParam(ftString, 'ID2', ptInput).AsString := '11112121'; 
Query.Prepare;

This is the specificity of working with parameters of SQL Server.