Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to pass array values as a procedure parameter?

I assume that my question may already exist in SO knowledgebase and I just couldn't find it. In this case, please guide me to the origin and vote to close this one as a duplicate. It is about passing array parameters to a procedure.

The below case is simple, intuitive and working:

procedure MyProc(const A: array of ShortString);
begin
//
end

Now I can call MyProc and directly pass strings to it:

MyProc(['no', 'trump', 'please']);

Now what if I declare a type of array of ShortString?

type
  TMyArray = array of ShortString;

procedure MyProc(const A: TMyArray);
begin
//
end

Is there any way to execute MyProc passing parameters of array type directly? I.e. using the same procedure call as above.

+bonus question:

type TMyRecord = record
  param, value: ShortString
end;

procedure MyProc(const R: TMyRecord);
begin
//
end

Is there any technique to call a procedure and pass values of a type record? Something like (pseudocode):

MyProc((TMyRecord.param='aaa', TMyRecord.value='bbb'));

The reason I ask: I don't want to declare a variable and set it up. Instead I want to pass array/record values directly as a procedure parameter.

like image 900
Interface Unknown Avatar asked Sep 19 '16 12:09

Interface Unknown


People also ask

How do I pass a list as a parameter in a stored procedure?

CREATE FUNCTION dbo. SplitInts ( @List VARCHAR(MAX), @Delimiter VARCHAR(255) ) RETURNS TABLE AS RETURN ( SELECT Item = CONVERT(INT, Item) FROM ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)') FROM ( SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter, '</i><i>') + '</i>'). query('.

Can we use array in stored procedure?

Arrays are a convenient way of passing transient collections of data between an application and a stored procedure or between two stored procedures. Within SQL stored procedures, arrays can be manipulated as arrays in conventional programming languages.

Can we pass array in SQL?

Basically you can't - SQL Server has no array type - ANSI SQL 92 does not specify array support. You could simulate an array by passing one or more varchar(255) fields with comma-separated values and then use a WHILE loop with PATINDEX and SUBSTR to extract the values.


2 Answers

As others said, it can be done in Delphi XE7 and later. In previous versions, array types can't be passed directly as parameter.

If you can't do without this "comfort".. As an "ugly" solution, you could use an intermediate function which copies a dynamic array into its corresponding array type:

function AsTypeArray(const MyArray : array of ShortString) : TMyArray;
begin
  SetLength(Result, Length(MyArray));
  Move(MyArray[Low(MyArray)], Result[0], Length(MyArray) * SizeOf(MyArray[Low(MyArray)]));
end;

So you could write:

MyProc(AsTypeArray(['no', 'trump', 'please']));

I don't like this solution, but if you can't upgrade to Delphi XE7 or later and you absolutely want to pass array type parameters directly, I think this is the only way.

Note that you can pass TMyArray objects as parameters to functions defined for accepting dynamic arrays:

procedure MyProc(const A: array of ShortString);
//...

var
  Arr : TMyArray;
begin
  //...
  MyProc(Arr); //It won't raise any error/warning
end;

Also for records, an intermediate function which returns the record type is needed.

function MyRecord(Param : ShortString; Value : ShortString) : TMyRecord;
begin
  Result.Param := Param;
  Result.Value := Value;
end;

Then you'll be able to pass your record without declaring a variable.

MyProc(MyRecord('aaa', 'bbb'));

Also in last Delphi's versions, I think there's no other way.

like image 132
Fabrizio Avatar answered Sep 22 '22 16:09

Fabrizio


Yes, what you have written works in Delphi XE7 and higher.

program Project1;

{$APPTYPE CONSOLE}

type
  TMyArray = array of ShortString;

procedure MyProc(const A: TMyArray);
var
  s : ShortString;
begin
  for s in A do WriteLn(s);
end;

begin
  MyProc(['no', 'trump', 'please']);
  ReadLn;
end.

Output :

no
trump
please

XE7 introduced new dynamic array initialization syntax that allows function calls like the one above. Prior to that you need to initialize with an explicit constructor like :

 MyProc(TMyArray.Create('no', 'trump', 'please'));

For the record, you need a constructor or other method to return the record type. This works :

program Project1;

{$APPTYPE CONSOLE}

type
  TMyRecord = record
   param: ShortString;
   value: ShortString;
   constructor Create(AParam, AValue : ShortString);
  end;

constructor TMyRecord.Create(AParam: ShortString; AValue: ShortString);
begin
  param := AParam;
  value := AValue;
end;

procedure MyProc(const R: TMyRecord);
begin
  WriteLn(R.param);
  WriteLn(R.value);
end;

begin
  MyProc(TMyRecord.Create('foo', 'bar'));
  ReadLn;
end.

Output:

foo
bar

like image 36
J... Avatar answered Sep 22 '22 16:09

J...