0

I am trying to pass an array of string as a parameter to SQL Server.

Here is the code I have:

SetLength(TimeArr, (EndTimeItemIndex - StartTimeItemIndex) + 1);

for i := StartTimeItemIndex to EndTimeItemIndex do
begin
  TimeArr[i - StartTimeItemIndex] := CBBookingTimeStart.Text;
  CBBookingTimeStart.ItemIndex := CBBookingTimeStart.ItemIndex + 1;
end;

DM.QRYTempProc.SQL.Add('AND (StartTime in (:XStart) OR EndTime in (:XEnd))');
DM.QRYTempProc.ParamByName('XStart').Value := TimeArr;
DM.QRYTempProc.ParamByName('XEnd').Value := TimeArr;

I get the error:

parameter [XSTART] data type is unknown

Any ideas how i pass an array of string as a parameter to SQL Server?

kobik
  • 21,001
  • 4
  • 61
  • 121
Budski
  • 5
  • 5
  • 1
    I doubt whether you will be able to pass an array of string: The SqlSever engine is expecting a comma-delimited list of values, and your QRYTempProc component, whatever it is, is unlikely to know how to convert one into the other. – Alex James Oct 26 '17 at 14:20
  • 1
    In recent Delphi versions you can write `string.Join(',', TimeArr);` to get that comma separated list. – Uwe Raabe Oct 26 '17 at 14:30
  • @UweRaabe You'd surely also need to escape any commas in the list – David Heffernan Oct 26 '17 at 14:34
  • 2
    You can't do that *with parameters*. The `IN` operator expects separate parameters e.g. `IN (:XStart1, :XStart2, :XStart3)` – kobik Oct 26 '17 at 14:36
  • sql server has that posibility, but it takes a bit more work. Here is an example (but the client code is in c# not delphi) https://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure – GuidoG Oct 26 '17 at 15:43

0 Answers0