Skip to Content
0
Former Member
Jun 21, 2016 at 12:08 PM

Dynamic SQL execution with in Read only procedure

589 Views

Hi,

I have a scenario where i need to execute the SELECT query (Which will be generated on the fly) with in a read only procedure WITH RESULT VIEW and then return the result as out parameter.

I tried the below but it did not work. Do we have any other approaches for this ?

create procedure PROC_TEST_DLS(out wrapOut CONTRACT_TYPE)

LANGUAGE SQLSCRIPT

READS SQL DATA WITH RESULT VIEW CONTRACT_SECURE AS

VAR_DLS_QUERY NVARCHAR(5000) DEFAULT '(1=1)';

v_user_name NVARCHAR(500);

BEGIN

CALL getapplicationcontext('USER_NAME',v_user_name);

SELECT P_QUERY into VAR_DLS_QUERY FROM getDLSFilter('CONTRACT',:v_user_name);

wrapOut = select * from :VAR_DLS_QUERY ;

END;

Error: scalar type is not allowed: line 13 col 27 (at pos 446)

I need to put this in Result view procedure as i need this code to be executed when we just say "select * from CONTRACT_SECURE".

So this is basically applying some restrictions for the user to see only eligible data.

I there any better idea here ?

Thanks

Venkatesh