SQL Server / Delphi : how to transfer a list of integers as a parameter using a table variable type?

Question

I'm using DELPHI XE6 with FireDAC components and has a SQL Server database server.

In the database, I have a stored procedure that takes two parameters, one integer and then a table variable that takes just one columns of type integer.

The table variable can contain from zero to multiple integers.

The head of the procedure:

/*
Declare @Temp dbo.LIP
insert into @Temp
Values(10901), (10902), (10903), (10904), (10905), (10912)

exec [dbo].[sp_lager_PkgList] 19, @Temp
*/
ALTER PROCEDURE [dbo].[sp_lager_PkgList]  
    @OwnerNo INT, 
    @LIPVariable dbo.LIP READONLY
AS
BEGIN

The outcommented part contains code that I used to check that the procedure is working ok - and it does.

In Delphi I did like this:

procedure TForm1.setUpParams;
var
  sLIPNo: string;
  ii: Integer;
begin
  try
    spMain.Prepare;
    spMain.Params.ParamByName('@OwnerNo').AsInteger := StrToInt(edtPOwner.Text);
    with spMain.Params.ParamByName('@LIPVariable') do
    begin
      DataType := ftDataSet;
      DataTypeName := 'dbo.LIP';//'LGlager_PkgList.@LIPVariable';
    end;
    fdmtblLIPNo.Active := false;
    fdmtblLIPNo.Active := True;
    fdmtblLIPNo.BeginBatch();
    try
      for sLIPNo in mmoPLIPs.Lines do
      begin
        fdmtblLIPNo.Append;
        try
          fdmtblLIPNo.FieldByName('LIPNo').AsInteger := StrToInt(sLIPNo);
          fdmtblLIPNo.Post;
        finally

        end;
      end;
    finally
      fdmtblLIPNo.EndBatch;
      ii := spMain.Params.ParamByName('@OwnerNo').AsInteger;
      mmoDebug.Lines.Add(IntToStr(ii));
    end;
    spMain.ParamByName('@LipVariable').AsDataSet := fdmtblLIPNo;

  finally
  end;
end;

When I later execute this procedure in the delphi program none of the parameters is transfered. I have checked that the procedure is executed so that part works.

What am I doing wrong? What should I do?

This is the script for dbo.LIP, it's not directly rocket science..

CREATE TYPE [dbo].[LIP] AS TABLE(
    [LIPNo] [int] NULL
)

Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Apr 27 '18 at 14:32
@marc_s On another side note, at least I doubt Microsoft will use `lager` in a stored procedure name in the future =) — Jerry Dodge, Apr 27 '18 at 14:37
@JerryDodge: agreed - likelihood is extremely slim - but the `sp_` also causes additional, unnecessary look ups for the procedure name, slowing things down - just better to avoid it altogether! — marc_s, Apr 27 '18 at 14:38
Thank you all, good to know I've already removed that prefix but sadly, it didn't change anything in my problem. — larand, Apr 27 '18 at 17:07
Could not because it's irrelevant and at least you're setting your parameter data type to `ftObject` instead of `dtDataSet`. That `DataTypeName` is also wrong. — Victoria, Apr 27 '18 at 17:16
Yes, I found this solution in an old thread but later I realized that there where a better type. But you also say that the DatatypeName is also wrong. That's interesting b'cause the old solution gave me that example and even if I was in doubt I had no other solution myself. Can you explain what this should be? — larand, Apr 27 '18 at 17:29
`DataTypeName` is `dbo.LIP`. But I guess you want to use the data type of the parameter. I'm not sure in this. However, I smell something wrong with FireDAC in the TVP usage. — Victoria, Apr 27 '18 at 19:35
I corrected the parameters and if I only use one record it works but if I have more I'll get the following exception:('[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0]Invalid string or buffer length', 0, nil, $1E80E28, False, 1200, 'Form1.spMain', $6D94B58, $39D2B20, 'dbo.LGlager_PkgList'#$D#$A) — larand, May 02 '18 at 08:49

Random_User · Answer 1 · 2018-04-27T15:17:53.983

What you are looking for is to pass an array, (this will help with google query) the way you can pass an array into SQL Server is via user defined table types.

Here is a primer: https://msdn.microsoft.com/en-us/library/bb386954(v=vs.100).aspx

This is close:

/*
Declare @Temp dbo.LIP
insert into @Temp
Values(10901), (10902), (10903), (10904), (10905), (10912)

exec [dbo].[sp_lager_PkgList] 19, @Temp
*/
ALTER PROCEDURE [dbo].[sp_lager_PkgList]  
    @OwnerNo INT, 
    @LIPVariable dbo.LIP READONLY

Can you post the DDL for dbo.LIP?

Also you will want to start Profiler and trace the call to the usp_, this will show you what you are passing to the usp_ to validate what you are passing in, then you will know if you have an issue with SQL or your app code.

From your code if you're able to run that locally and it runs, then most likely you do not have a SQL issue, Profiler will answer the question for you, also you might be able to use fiddler if that is easier.

Hungarian notation is often bad practice, for triggers and user stored procedures it is often common and an okay practice. I would suggest usp_ rather than sp_.

So, the SQL looks fine off the cuff, you need to know what is being passed to SQL (Profiler or Fiddler). I'm guessing it is an issue in the application passing / calling the usp_; or I'm not sure if you can pass the output to a flat file and post that — Random_User, Apr 27 '18 at 17:22

SQL Server / Delphi : how to transfer a list of integers as a parameter using a table variable type?

1 Answers1