Call a stored procedure with user-defined table type parameters

Hi, I have to call a stored procedure that has user-defined table type input parameters. The procedure and the types are defined on a sqlserver database.

Is it possible to call it with outsystems? It seems that it is possible to pass only basic types.  

thanks


Hi Mauro,


From what I know basic types and entity identifier types are allowed in Advanced SQL as parameters which can also be passed as input parameters for a stored procedure execution.


Not sure what you mean by user-defined table type input parameters, is the procedure expecting an input of entity (table) type? Like passing a full table record as input? If so, maybe procedure logic should be changed, to accept an entity identifier and get the record in its logic instead. It's never a good idea to pass as input parameters to screens/actions etc. a full entity record.


Regards,

Bogdan

Hi Mauro,

you can take a different approach,  instead of calling the stored procedure directly from the SQL widget you can create one extension and inside that extension code (C# code) you can accept the compound type (Structure) and you can pass it to the stored procedure in the form of the data table.


DataTable dt = new DataTable();

dt.Columns.Add("Attr1", typeof (string));

dt.Columns.Add("Attr2", typeof (string));


SqlParameter sqlparam = new SqlParameter("@userdefinedtabletypeparameter", SqlDbType.Structured)

{

    TypeName = "dbo.userdefinedtabletype",

    Value = dt

};

sqlComm.Parameters.Add(sqlparam);

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.