Skip to Content

Schema names hard coded into procedures

Hi All,

I have 4 ECC systems coming in via SLT to four target schemas. Lets call them schema A, B, C and D. I have the same table..lets say VBAK in all of them.

Now I have a stored procedure which takes an input parameter called schemaName


The procedure has logic like:

if schemaName  = 'A'

{

select from A.VBAK

}

elseif schemaName  = 'B'

{

select from B.VBAK

}

elseif schemaName  = 'C'

{

select from C.VBAK

}

elseif schemaName  = 'D'

{

select from D.VBAK

}

Now the problem is that A,B,C,D map to M,N,O,P in the Quality environment and W,X,Y,Z in the prod.

Hard coded values won't undergo schema mapping upon transport and mentioning a default schema doesn't help since I have multiple schemas.

Confused on what to do.. How do i make the schema names switch in the If condition and in the select statement?

Let me know your thoughts on this.

Thanks,

Shyam

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 19, 2015 at 06:56 AM

    Hi,


    Use public synonyms for the same.

    https://scn.sap.com/thread/3568988.

    Br

    Sumeet

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 19, 2015 at 05:31 AM

    Hi,

    you can create a custom table to maintain the system id with schema names & can get the schema based on system id at run time.

    or

    Use variant in the system.

    Br,

    Amit

    Add comment
    10|10000 characters needed characters exceeded

    • You can also use :

      CASE system_id.

      WHEN 'DEV'." example only, u can use actual values here.

      if....else statement for DEV schema.

      WHEN 'QAS'

      if....else statement for QAS schema.

      WHEN 'PRD'

      if....else statement for PRD schema.

      ENDCASE.

      It could not be a best solution, but if u like u can use it.

      Br,

      Amit

  • avatar image
    Former Member
    Oct 19, 2015 at 06:48 AM

    Hi Shyam,

    You can create some default schema and create synonyms in that schema to maintain the mappings to the respective objects of respective schema.

    These synonyms can be used in your procedure which will take out entire if else logic.

    When migrating to different systems . These synonym definition can be changed to point to respective schemas which will avoid code change as well.

    Regards,

    Anil

    Add comment
    10|10000 characters needed characters exceeded