Invalid Column Name

[#1]

Hi Experts,

I am getting the below error while runningthe SP . The sql query highlighted in BOLS is givingthe problem. Table name is not populated properly I guess. Could anyone help me in this.

SAP DBTech JDBC: [260]: invalid column name:  [260] "Schema1"."SAMPLE": line 24 col 3 (at pos 740): [260] (range 3) invalid column name exception: invalid column name: 1002: line 1 col 47 (at pos 46)

CREATE PROCEDURE SAMPLE (IN ZSCHEMA NVARCHAR(100))
LANGUAGE SQLSCRIPT AS

Valuecount     NVARCHAR(200);
RestTablname   NVARCHAR(200);
RestColname  NVARCHAR(200);
RestColvalue NVARCHAR(500);

CURSOR SAMPLECURSOR FOR       
     SELECT  TABLENAME, FIELDNAME, FIELDVALUE
            FROM  "SREELATR"."SAMPLETABLE";
BEGIN

EXEC 'SET SCHEMA '||:ZSCHEMA;
OPEN SAMPLECURSOR
FOR currRow as SAMPLECURSOR DO
       RestTablname := currRow.TABLENAME;
       RestColname  := currRow.FIELDNAME;
       RestColvalue := currRow.FIELDVALUE;   
               
    Valuecount := 'SELECT COUNT(*) FROM Schema1.'||:RestTablname||' WHERE '||:RestColname||' IN ("'||:RestColvalue||'")';
   
  EXECUTE IMMEDIATE :Valuecount; 
 
IF Valuecount > 0
   THEN
    UPDATE  "SREELATR"."SAMPLETABLE";
      SET RESULT = 'PASS';
   ELSE
    UPDATE  "SREELATR"."SAMPLETABLE";
     SET RESULT = 'FAIL';
END IF;
 
END FOR;
END;

CALL SAMPLE ('Schema1');

[lbreddemann]

Alright,

first of all: is there a matching column name in your table?

Then, please reconsider your approach here.

Yes, it appears to be super-clever to do the "flexible schema" database programming, but ultimately it will bite back on so many levels down the road.

DB Engine built-in optimizations - don't work.

Schema separated dependencies - nope, you just created a big entangled ball of code

Half-way easy to understand and debug application structure - you're kiddin', right?

...

So, please do yourself a big favor and try not to do this design.

Also: in SQL Script it's not possible to assign results from dynamic SQL to table variables.

- Lars