0

We have this option to pass table valued parameters in SQL Server Stored procedure.

For example :

CREATE OR ALTER PROCEDURE [dbo].[TestProcedure]
(
    @Id INT,
    @StartDate DATETIME,
    @EndDate DATETIME,  
    @TestIds1 dbo.UserDefinedTableType1 READONLY,
    @TestIds2 dbo.UserDefinedTableType2 READONLY
)

Do we have a similar option in MySQL?

Currently I am passing comma separated string as Text parameter and extracting from that.

For example, if I want

Id

1

2

I am passing,

'[{"Id":1},{"Id":2}]'

CALL TestProcedure ( ID1, '[{"Id":1},{"Id":2}]', '[{"Id2":1}]', 'No' );

and extracting like

SELECT *
    FROM JSON_TABLE(Ids,"$[*]"
            COLUMNS(Id INT PATH "$.Id")) AS XX;
Learner
  • 1
  • 1
  • Use temptable with predefined name. – Akina Nov 28 '22 at 05:18
  • Could you please explain a little bit more? – Learner Nov 28 '22 at 05:20
  • When you need to provide a table to your procedure then you firstly create temptable named, for example, `TestProcedure_temptable1` of needed structure, insert the data which must be provided into SP to this table, then call SP. SP uses this temptable data. The temptable name guarantees that you won't interfere with another code. After SP executed you may drop this temptable, or it will be autodropped during connection close. – Akina Nov 28 '22 at 05:24
  • Similar to https://stackoverflow.com/questions/28498760/how-do-i-use-a-mysql-stored-procedure-from-c-sharp-which-sends-a-table-as-input. – Tim Jarosz Nov 28 '22 at 05:44

0 Answers0