0

How do I call a stored procedure that takes a table type as an argument from OTL?

Here is how my database looks like:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DROP PROCEDURE IF Exists dbo.spTest
DROP TYPE IF Exists dbo.List

--- Create UDTT
CREATE TYPE dbo.List as TABLE
(
    k varchar(32) NOT NULL,
    v float NOt NULL
)
GO

CREATE PROCEDURE dbo.spTest
    @list List READONLY 
AS
    BEGIN
        SELECT * from @list     
    END
GO

--- Call spTEST

DECLARE @l List
INSERT INTO @l(k, v)
VALUES ('A', 1.0);

EXECUTE [dbo].[spTest]
      @list = @l
GO

I tried to code something up in C++ but I am not sure how to create the list argument or specify the list type

otl_stream i(50, // buffer size
    "{call dbo.spTest("
    " :list<char[32],in> "
    ")}",    
    db, // connect object
  );

Update: The auditor of OTL Skuchin told me in a private email that is not possible. "OTL doesn't support those. The best you can do is to insert a bunch of rows into a temp table / table variable via a regular insert and read from it in your SP"

Damian
  • 4,395
  • 4
  • 39
  • 67
  • Looks like you want a table value parameter... https://msdn.microsoft.com/en-us/library/bb510489.aspx – S3S Nov 29 '16 at 15:42
  • Yes, I clarified my question. It is called a table type argument. How can I do that with OTL? – Damian Nov 29 '16 at 15:44
  • I think I need to send a BLOB as an argument to the database? – Damian Nov 29 '16 at 15:56
  • so... you're creating a data set... to send to sql... to select the dataset? – DForck42 Nov 29 '16 at 17:08
  • Yes, this is just an example. I will use the list to insert information into two tables (we can call them header and info). I could just do that with two stored procs. One for inserting in header and one for info. – Damian Nov 30 '16 at 06:13

0 Answers0