Exemplo de procedure SQL e HANA¶

Confira abaixo a customização da procedure GetProcRef de acordo os campos criados no exemplo anterior:

Procedure SQL

ALTER PROCEDURE [dbo].[GetProcRef]
    @pCompanyDb nvarchar(128),
    @pObjId int,
    @pTblSuffix nvarchar(3),
    @pDocEntry int
AS
BEGIN

        Declare @query nvarchar(max)

    Set @query=
    'Select
        nProc = U_nProc
        ,IndProc = U_IndProc

    From ['+@pCompanyDb+ ']..[O'+@pTblSuffix+']T1 With (NOLOCK)

    Where
         T1.DocEntry ='+convert(nvarchar(30),@pDocEntry)
    exec (@query)

END

Procedure HANA

ALTER PROCEDURE "GetProcRef"

(

    pCompanyDb nvarchar(128),

    pObjId int,

    pTblSuffix nvarchar(3),

    pDocEntry int

)

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

AS

BEGIN

    Declare query nvarchar(5000);

    query :=

    'Select

        CAST ("U_nProc" AS NVARCHAR) AS "nProc",

        CAST ("U_indProc" AS NVARCHAR) AS "indProc"

    From '||:pCompanyDb||'.O'||pTblSuffix||' T1

    Where

        T1."DocEntry"='||:pDocEntry;

    EXECUTE IMMEDIATE query;

END