Parameter for IN () of Query

[GNiessen]

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?

[AndreyZ]

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:

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;

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

[GNiessen]

I was avoiding macros for the same reason that I don't generate the SQL dynamically. Though it is cleaner.

I would like to see how you would use Table-Valued Parameters in SDAC.

[AndreyZ]

Here is a code example:

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;

, where DeptNoType is a table-valued parameter defined as following:

Code: Select all

CREATE TYPE DeptNoType AS TABLE(
  DEPTNO INT
);

and DEPT is a table defined as following:

Code: Select all

CREATE TABLE DEPT (
  DEPTNO INT PRIMARY KEY,
  DNAME VARCHAR(14),
  LOC VARCHAR(13)
);

[GNiessen]

Thanks

That was what I thought it might look like. But I was trying to get away from hardcoded sql. Though this may look different to the SQL Server optimizer. I guess I will run some tests and see which works best.

[AndreyZ]

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:

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;

, where SP_SelectDept is a stored procedure defined as following:

Code: Select all

CREATE PROCEDURE SP_SelectDept
  @DeptNo DeptNoType READONLY
AS
BEGIN
  SELECT * FROM DEPT WHERE DEPTNO IN (SELECT * FROM @DeptNo)
END