how to capture return value from stored procedure?

[Former Member]

Hi All,

I want to capture the retun values from this procedure to a table - CALL SYS.GET_OBJECT_DEFINITION('SCHEMA_NAME', 'TABLE_NAME').

The below approach is not working -

Insert  into STG.STG_DDL

Call SYS.GET_OBJECT_DEFINITION('DWG', 'DWG_SITE')

;

Could you please have a look on the same?

Thank you,

Vijeesh

[former_member187673 · Accepted solution]

Hi Vijeesh, you may want to take a different approach here. There are no OUT params in this procedure so not sure if possible to capture the results. Instead you could query from sys.tables and sys.table_columns to get the definition of the table that you want and store in a table variable. Hope that makes sense.

Peter

[Former Member · Accepted solution]

Hi Peter,

Yes, that is a good idea. If this procedure is not going to help, I will try that option.

But, that is not going to be easy with a lot of different data type, default, null/not null scenarios. I had seen a thread for that in this portal.

My doubt is - when I execute this stored proc, I can see the DDLs in the output window. Can't we capture those details in someway?

CALL SYS.GET_OBJECT_DEFINITION('SCHEMA_NAME', 'TABLE_NAME').

Few options that I was thinking-

1. Declare a cursor for this procedure.

2. Include this Procedure inside another stored_procedure... etc..

Can we declare a cursor with stored proc?

Thanks a lot in advance,

Vijeesh

[former_member187673 · Accepted solution]

Yeah, I don't think this is possible with cursors either (open to correction on that). Ideally what you want is to find a table which stores the table def creation statement however I'm not sure if that is available in SYS schema or elsewhere or whether the GET_OBJECT_DEFINITION procedure generates that from the tables I've mentioned above. I haven't found anything in the SYS schema yet for this but take a look and see.

Maybe some internal SAP gurus could point you in right direction.

Peter

[sagarjoshi · Accepted solution]

sys.get_object_definition is built-in procedure and as per my information

Unfortunately, the results from the procedure sys.get_object_definition can not be used from SQLScript.

However I think it should be possible to capture this if you can write a python or java client application on top.

[Former Member]

Thanks a lot Everyone.

Considering the discussed options, and an approach explained in this thread - -http://scn.sap.com/thread/3291461  , I have written an SQL to build the Alter statements to add the columns & Constrains to table.

The below Query will provide the scripts to build a table in another environment.

select * from (

   select TABLE_name,'tbl_Create' column_name, 1 as position, 'CREATE TABLE '|| schema_name ||'.'|| table_name ||' (  DUMMY_CLMN INTEGER);' as SQLCMD
     from tableS
    where  schema_name ='DWG'
      and TABLE_name ='DWG_PRODUCTION_VOLUME_TRX'
UNION ALL

-- MASS change of NOT NULL COLUMNS
-- set to NOT NULL - character data type, double, decimal fixed - need the length but not the scale
   select TABLE_name,column_name, position + 100, 'ALTER TABLE '|| schema_name ||'.'|| table_name ||' ADD ('||column_name ||' '||data_type_name ||' (' || length ||') NOT NULL) ;' as SQLCMD
     from table_columns
    where is_nullable = 'FALSE'
      and schema_name ='DWG'
      and TABLE_name ='DWG_PRODUCTION_VOLUME_TRX'
      and data_type_name in ('VARCHAR', 'NVARCHAR', 'DOUBLE')
      and scale is NULL
UNION ALL

-- MASS change of NOT NULL COLUMNS
-- set to NOT NULL - character data type, double, decimal fixed - need the length but not the scale
   select TABLE_name,column_name, position + 100,'ALTER TABLE '|| schema_name ||'.'|| table_name ||' ADD ('||column_name ||' '||data_type_name ||' (' || length ||') );' as SQLCMD
     from table_columns
    where is_nullable = 'TRUE'
      and schema_name ='DWG'
      and TABLE_name ='DWG_PRODUCTION_VOLUME_TRX'
      and data_type_name in ('VARCHAR', 'NVARCHAR', 'DOUBLE')
      and scale is NULL
     
     
UNION ALL

-- set to NOT NULL - DECIMAL (FLOATING POINT)- needs length and scale
   select TABLE_name,column_name, position + 100, 'ALTER TABLE '|| schema_name ||'.'|| table_name ||' ADD ('||column_name ||' '||data_type_name ||' (' || length ||','|| scale ||') NOT NULL) ;' as SQLCMD
     from table_columns
    where is_nullable = 'FALSE' 
      and schema_name ='DWG'
      and TABLE_name ='DWG_PRODUCTION_VOLUME_TRX'
      and data_type_name in ('DECIMAL' )
      and scale is not null
UNION ALL

-- set to NOT NULL - DECIMAL (FLOATING POINT)- needs length and scale
   select TABLE_name,column_name, position + 100, 'ALTER TABLE '|| schema_name ||'.'|| table_name ||' ADD ('||column_name ||' '||data_type_name ||' (' || length ||','|| scale ||') ) ;' as SQLCMD
     from table_columns
    where is_nullable = 'TRUE'
      and schema_name ='DWG'
      and TABLE_name ='DWG_PRODUCTION_VOLUME_TRX'
      and data_type_name in ('DECIMAL' )
      and scale is not null

UNION ALL

-- set to NOT NULL - DECIMAL (FLOATING POINT)- needs length and null scale
   select TABLE_name,column_name, position + 100, 'ALTER TABLE '|| schema_name ||'.'|| table_name ||' ADD ('||column_name ||' '||data_type_name ||' (' || length ||') NOT NULL) ;' as SQLCMD
     from table_columns
    where is_nullable = 'FALSE' 
      and schema_name ='DWG'
      and TABLE_name ='DWG_PRODUCTION_VOLUME_TRX'
      and data_type_name in ('DECIMAL' )
      and scale is  null
UNION ALL

-- set to NOT NULL - DECIMAL (FLOATING POINT)- needs length and null scale
   select TABLE_name,column_name, position + 100, 'ALTER TABLE '|| schema_name ||'.'|| table_name ||' ADD ('||column_name ||' '||data_type_name ||' (' || length ||') NOT NULL) ;' as SQLCMD
     from table_columns
    where is_nullable = 'TRUE'
      and schema_name ='DWG'
      and TABLE_name ='DWG_PRODUCTION_VOLUME_TRX'
      and data_type_name in ('DECIMAL' )
      and scale is  null
           
UNION ALL
-- set to NOT NULL -  DATE | TIME | SECONDDATE | TIMESTAMP | TINYINT | SMALLINT | INTEGER - don't need length  or scale
select TABLE_name,column_name, position + 100, 'ALTER TABLE '|| schema_name ||'.'|| table_name ||' ADD ('||column_name ||' '||data_type_name ||' NOT NULL) ;' as SQLCMD
   from table_columns
  where is_nullable = 'FALSE'
    and schema_name ='DWG'
    and TABLE_name ='DWG_PRODUCTION_VOLUME_TRX'
    and data_type_name in ('DATE', 'LONGDATE', 'TIME', 'SECONDDATE', 'TIMESTAMP', 'TINYINT', 'SMALLINT', 'INTEGER' )
--   and scale is not null   
      
           
UNION ALL
-- set to NOT NULL -  DATE | TIME | SECONDDATE | TIMESTAMP | TINYINT | SMALLINT | INTEGER - don't need length  or scale
select TABLE_name,column_name, position + 100, 'ALTER TABLE '|| schema_name ||'.'|| table_name ||' ADD ('||column_name ||' '||data_type_name ||' ) ;' as SQLCMD
   from table_columns
  where is_nullable = 'TRUE'
    and schema_name ='DWG'
    and TABLE_name ='DWG_PRODUCTION_VOLUME_TRX'
    and data_type_name in ('DATE', 'LONGDATE', 'TIME', 'SECONDDATE', 'TIMESTAMP', 'TINYINT', 'SMALLINT', 'INTEGER' )
--    and scale is not null

UNION ALL
select table_name, 'PK' AS column_name, 9990, 'ALTER TABLE '||table_name||' ADD CONSTRAINT Primary_key PRIMARY KEY ('||PK_COLUMN_NAME1||
case when  PK_COLUMN_NAME2 is null then  ' ' else ','|| PK_COLUMN_NAME2 end ||
case when  PK_COLUMN_NAME3 is null then  ' ' else ','|| PK_COLUMN_NAME3 end ||
case when  PK_COLUMN_NAME4 is null then  ' ' else ','|| PK_COLUMN_NAME4 end ||');'
from
(SELECT DISTINCT C1.table_name , C1.COLUMN_NAME AS PK_COLUMN_NAME1, C2.COLUMN_NAME AS PK_COLUMN_NAME2, C3.COLUMN_NAME AS PK_COLUMN_NAME3, C4.COLUMN_NAME AS PK_COLUMN_NAME4
                     FROM (SELECT * FROM CONSTRAINTS WHERE POSITION=1 AND IS_PRIMARY_KEY = 'TRUE') C1
                     LEFT JOIN (SELECT * FROM CONSTRAINTS WHERE POSITION=2 AND IS_PRIMARY_KEY = 'TRUE') C2
                            ON C1.table_name = C2.table_name
                     LEFT JOIN (SELECT * FROM CONSTRAINTS WHERE POSITION=3 AND IS_PRIMARY_KEY = 'TRUE') C3
                            ON C2.table_name = C3.table_name
                     LEFT JOIN (SELECT * FROM CONSTRAINTS WHERE POSITION=4 AND IS_PRIMARY_KEY = 'TRUE') C4
                            ON C3.table_name = C4.table_name
                     LEFT JOIN (SELECT * FROM CONSTRAINTS WHERE POSITION=5 AND IS_PRIMARY_KEY = 'TRUE') C5
                            ON C4.table_name = C5.table_name
                     ) PK     
where table_name = 'DWG_PRODUCTION_VOLUME_TRX'

UNION ALL
select table_name, 'UK' AS column_name, 9991,  'ALTER TABLE '||table_name||' ADD CONSTRAINT UNIQUE ('||UK_COLUMN_NAME1||
case when  UK_COLUMN_NAME2 is null then  ' ' else ','|| UK_COLUMN_NAME2 end ||
case when  UK_COLUMN_NAME3 is null then  ' ' else ','|| UK_COLUMN_NAME3 end ||
case when  UK_COLUMN_NAME4 is null then  ' ' else ','|| UK_COLUMN_NAME4 end ||');'
FROM
(SELECT DISTINCT C1.table_name , C1.COLUMN_NAME AS UK_COLUMN_NAME1, C2.COLUMN_NAME AS UK_COLUMN_NAME2, C3.COLUMN_NAME AS UK_COLUMN_NAME3, C4.COLUMN_NAME AS UK_COLUMN_NAME4
                     FROM (SELECT * FROM CONSTRAINTS WHERE POSITION=1 AND IS_PRIMARY_KEY = 'FALSE') C1
                     LEFT JOIN (SELECT * FROM CONSTRAINTS WHERE POSITION=2 AND IS_PRIMARY_KEY = 'FALSE') C2
                            ON C1.table_name = C2.table_name
                     LEFT JOIN (SELECT * FROM CONSTRAINTS WHERE POSITION=3 AND IS_PRIMARY_KEY = 'FALSE') C3
                            ON C2.table_name = C3.table_name
                     LEFT JOIN (SELECT * FROM CONSTRAINTS WHERE POSITION=4 AND IS_PRIMARY_KEY = 'FALSE') C4
                            ON C3.table_name = C4.table_name
                     LEFT JOIN (SELECT * FROM CONSTRAINTS WHERE POSITION=5 AND IS_PRIMARY_KEY = 'FALSE') C5
                            ON C4.table_name = C5.table_name
                     ) UK    
where table_name = 'DWG_PRODUCTION_VOLUME_TRX'

UNION ALL

SELECT REFERENCED_TABLE_NAME AS table_name,'FK' AS column_name, 9992,
'ALTER TABLE DWG.'||TABLE_NAME||' ADD FOREIGN KEY ( '||COLUMN_NAME||' ) REFERENCES '|| REFERENCED_TABLE_NAME||'('||COLUMN_NAME||' ) ON UPDATE CASCADE ON DELETE RESTRICT;'
FROM REFERENTIAL_CONSTRAINTS
WHERE REFERENCED_TABLE_NAME = 'DWG_SITE'

UNION ALL

select TABLE_name,'tbl_ClmnDrop' column_name, 9995 as position, 'ALTER TABLE '|| schema_name ||'.'|| table_name ||' DROP (  DUMMY_CLMN );' as SQLCMD
from tableS
where  schema_name ='DWG'
  and TABLE_name ='DWG_PRODUCTION_VOLUME_TRX'

)
order by position;