Is it in R possible to use a user-defined-table-type (UDT) existing in the database to execute a stored procedure with a parameter based on this UDT?

Viewed 109

Is it possible in R (as in other languages) to do this with R-database-functionality:

my database contains stored procedures which use user-defined types (UDT, especially table-types) for some parameters (TVP). I would like to execute these stored procedures from my R program, but can't find a solution how to use the UDTs for the affected parameters. Maybe it's not supported in R as I know it for example in .NET?

Simple example (database: SQL Server):

The user-defined table type:

CREATE TYPE T_KEYS AS TABLE 
(
    key nvarchar(3) NOT NULL
)

A stored procedure using this UDT:

CREATE PROCEDURE SP_DoIt 
    (@name    NVARCHAR(4),
     @keylist T_KEYS READONLY)
AS
BEGIN 
    -- do something useful with the values of keylist
    SELECT something 
    FROM somewhere
    WHERE mykey IN (SELECT * FROM keylist);    

    RETURN
END

Executing the stored procedure in the SQL Server database context:

BEGIN
    DECLARE @somekeys AS T_KEYS

    INSERT INTO @somekeys 
    VALUES ('mX2'), ('x8U'), ('K2i')   

    EXEC SP_DoIt 'callaspadeaspade', @somekeys
end

What to do inside the R-program in general to execute this stored procedure?

(just template-code, using glue_sql is maybe not smart)

sql <- "exec SP_DoIt {name}, {keys};"
mykeys <- ** how to instantiate a list of T_KEYS-datatype ??? **
query <- glue::glue_sql(sql, 
                        name = 'callaspadeaspade', 
                        keys = mykeys,
                        .con = dbconnobject)

resultset <- DBI::dbSendQuery(connectionobject, query)
gotIt <- DBI::dbFetch(resultset)
DBI::dbClearResult(resultset)
0 Answers
Related