How to convert mssql user-defined table type into mysql UDT

Question

This is my mssql UDT

create type ConditionUDT as Table 
(
    Name varchar(150),
    PackageId int
);

This is my mssql Stored Procedure

create Procedure [dbo].[Condition_insert]
    @terms_conditions ConditionUDT readonly
as
    begin
    insert into dbo.condition (name, p_id)
    select [Name],[PackageId]
    from @terms_conditions;
    end

Answer 1

There is a workaround solution if you do not have any other choice but definitely migrate from sql server to mysql.

The closest structural predefined object that takes on many rows in mysql is an actual table. So you need 1 table per UDDT of sql server. Make sure you use a specific schema or naming conversion so you know those tables are UDDT emulations.

The idea is fill in the info, use them into the sp and then delete them. You need however to gurantee who reads what and that info are deleted after usage, consumed. So:

For any of those tables you need 2 columns, i suggest put them always first. That will be the key and the variable name. The key can be char(38) and use UUID() to get a unique identifier. It can also be int and use the connectionid() instead. Unique identifier is better however as ensures that nobody will ever use information not indented for him no matter what. The variable name will be the one used into the sql server parameter, just a string. This way:

You know what UDDT you use out of the table name. You know the identity of your process through the key. You know the 'variable' out of the name.

So, in your application code you: Begin transaction. Insert the data into the proper (UDDT emulator) tables using a key and the variable name(s) Supply to the stored procedure the key and the variable name(s). You can use the same key for many table type parameters within the same sp call. The stored procedure can now use that information as before from the UDDT variable using key and variable name as filters to query the proper UDDT emulated table. Delete the data you insert Commit On catch, rollback.

For simplicity your sp can read the data into temp table and you do not need to change a line of code from the original sql server sp for this aspect.

Transaction into your app code will help you make sure your temporary variable data will either be deleted or never committed no matter what goes wrong.