CODEWITHSUNDEEP
sqlhana
sveer
sveer

Reputation: 472

INPUT a List to stored procedure

I get a list of elments from R and I have to obtain the records from database that belong to a the list of elements.

INPUT:

'12345','23456', '34567', '45678'

PROCEDURE:

   CREATE PROCEDURE "SCHEMA"."GET_RECORDS" (IN LIST (Type), OUT RECORDS tt_records)
   LANGUAGE SQLSCRIPT
    SQL SECURITY INVOKER 
READS SQL DATA AS
    BEGIN
    RECORDS = select * from TABLE where ids in :LIST
    END;

How can I provide such a list to the proceudre?

Upvotes: 0

Views: 1803

Answers (2)

Lars Br.
Lars Br.

Reputation: 10388

Handing over lists of parameters to SQLScript is a bit tricky as there is no straight-forward native construct for that.

One way to do it is to use the APPLY_FILTER function and to "smuggle" the list as a string parameter.

In my example I read from a table CUSERS and I create a filter condition for APPLY_FILTER that filters column USER_ID via an IN ( ) clause.

Removing the single quotes (' ') from the list is to avoid implicit type conversion when executing the query. Leaving the single quotes in place would make the IN () clause make look like this:
IN ( '<1st value>', '<2nd value>', '<3rd value>', ...)
instead of
IN (<1st value>, <2nd value>, <3rd value>, ...).

CREATE PROCEDURE  "GET_RECORDS" (IN id_list VARCHAR(4000)
                                , OUT RECORDS tt_cusers)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER 
    READS SQL DATA AS
BEGIN
declare _filter VARCHAR(4000);

    _users = select * from cusers;

    -- APPLY_FILTER expects a proper WHERE condition, so adding the column to filter
    -- and the IN () expression is necessary.
    -- 
    -- the the id_list comes in with single quotes, let's remove those

    _filter = 'USER_ID in (' || replace (:id_list, '''', '') ||')';
    RECORDS = APPLY_FILTER(:_users, :_filter);    
end;    

call get_records (?, ?)

-- this 'list' is to be used as a single parameter value
-- '131072', '161223', '131074'

A slightly more comfortable approach for getting the data out from SAP HANA into R can be using a table typed user-defined function (UDF) instead. The main difference here is that the calling statement is a simple SELECT and the result is simply the resultset of this SELECT.

CREATE function  "FGET_RECORDS" (IN id_list VARCHAR(4000))
returns  tt_cusers
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER 
    READS SQL DATA AS
BEGIN
declare _filter VARCHAR(4000);

    _users = select * from cusers;

    -- APPLY_FILTER expects a proper WHERE condition, so adding the column to filter
    -- and the IN () expression is necessary.
    -- 
    -- the the id_list comes in with single quotes, let's remove those

    _filter = 'USER_ID in (' || replace (:id_list, '''', '') ||')';
    _result = APPLY_FILTER(:_users, :_filter);    
    RETURN :_result;
end;    


select * from fget_records (? );

In R (or in any other client) make sure to use bind variables when using this construct. Otherwise handling the different string quote-mechanisms can become cumbersome.

See the documentation on APPLY_FILTER here.

Upvotes: 2

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Use a User Defined Data Type.

First Create A User Defined Data Type

Database Node > Programmability > Types > User-Defined Table Types

Script :

CREATE TYPE dbo.MyTableType AS TABLE 
(
    ID INT
)

Create a Parameter in your procedure with the above type

CREATE PROCEDURE usp_InsertMessages 
(
    @MyParameter MyTableType READONLY
)
AS
BEGIN

    INSERT INTO MyTable
    (
       id
    )
    SELECT 
       id
       FROM @MyParameter 

END

Upvotes: 1

Related Questions