Parameter for IN () of Query
Parameter for IN () of Query
I have a query that is hard coded to :
SELECT TestName FROM Tests WHERE TestID IN (1,2,4,8,51);
{It is actually concatenated strings at run time}
But I would like to us a parameter, like:
SELECT TestName FROM Tests WHERE TestID IN (:InList);
What type could I use for this (ftArray or ftVariant)?
Any code examples?
SELECT TestName FROM Tests WHERE TestID IN (1,2,4,8,51);
{It is actually concatenated strings at run time}
But I would like to us a parameter, like:
SELECT TestName FROM Tests WHERE TestID IN (:InList);
What type could I use for this (ftArray or ftVariant)?
Any code examples?
Hello,
You cannot use array parameters because SQL Server doesn't support arrays. To solve the problem, you can use macros instead of parameters. Here is a code example:Also, if you are using SQL Server 2008 and higher, you can use Table-Valued Parameters. For more information, please read the "Using Table-Valued Parameters" article of the SDAC documentation. You can find more information about Table-Valued Parameters at http://msdn.microsoft.com/en-us/library/bb510489.aspx
You cannot use array parameters because SQL Server doesn't support arrays. To solve the problem, you can use macros instead of parameters. Here is a code example:
Code: Select all
MSQuery.SQL.Text := 'SELECT TestName FROM Tests WHERE TestID IN (&InList);';
MSQuery.MacroByName('InList').AsString := '1'',''2'',''4'',''8'',''51';
MSQuery.Open;
Here is a code example:, where DeptNoType is a table-valued parameter defined as following:and DEPT is a table defined as following:
Code: Select all
procedure TMainForm.BitBtnClick(Sender: TObject);
begin
MSQuery.SQL.Clear;
MSQuery.SQL.Add('DECLARE @DeptNo AS DeptNoType;');
MSQuery.SQL.Add('INSERT INTO @DeptNo (DEPTNO) VALUES(1);');
MSQuery.SQL.Add('INSERT INTO @DeptNo (DEPTNO) VALUES(2);');
MSQuery.SQL.Add('INSERT INTO @DeptNo (DEPTNO) VALUES(4);');
MSQuery.SQL.Add('INSERT INTO @DeptNo (DEPTNO) VALUES(8);');
MSQuery.SQL.Add('INSERT INTO @DeptNo (DEPTNO) VALUES(51);');
MSQuery.SQL.Add('SELECT * FROM DEPT WHERE DEPTNO IN (SELECT * FROM @DeptNo);');
MSQuery.Open;
end;
Code: Select all
CREATE TYPE DeptNoType AS TABLE(
DEPTNO INT
);
Code: Select all
CREATE TABLE DEPT (
DEPTNO INT PRIMARY KEY,
DNAME VARCHAR(14),
LOC VARCHAR(13)
);
If you don't want to use such SQL code in your application, you can put it to a stored procedure. Here is a code example that demonstrates it:, where SP_SelectDept is a stored procedure defined as following:
Code: Select all
procedure TMainForm.BitBtnClick(Sender: TObject);
begin
MSTableData.TableTypeName := 'DeptNoType';
MSTableData.Open;
MSTableData.Append;
MSTableData.FieldByName('deptno').AsInteger := 1;
MSTableData.Post;
MSTableData.Append;
MSTableData.FieldByName('deptno').AsInteger := 2;
MSTableData.Post;
MSStoredProc.StoredProcName := 'SP_SelectDept';
MSStoredProc.PrepareSQL;
MSStoredProc.ParamByName('DeptNo').AsTable := MSTableData.Table;
MSStoredProc.Open;
end;
Code: Select all
CREATE PROCEDURE SP_SelectDept
@DeptNo DeptNoType READONLY
AS
BEGIN
SELECT * FROM DEPT WHERE DEPTNO IN (SELECT * FROM @DeptNo)
END