Skip to Content
avatar image
Former Member

Passing database name to stored procedure

Hi everyone,

Please tell me, how to send database name to the stored procedure.

I tried below code :

CREATE PROCEDURE Test(IN CompanyDataBase nvarchar(50)) LANGUAGE SQLSCRIPT AS

Begin

select * from '||:CompanyDataBase||'."ORDR" ';

end;

But I got following error :(

sql syntax error: incorrect syntax near "||:CompanyDataBase||":

Please help me guys

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Nov 21, 2017 at 03:08 AM

    What you would need here is called "dynamic SQL". There's documentation on that, so I won't go into the details here.

    But the thing is, you really should not be doing that, especially when you don't even know what this concept is called.

    I know, it looks so good... "just write this code once, and it will run on any schema, database, tenant...", right?

    Wrong.

    This is a common but tremendously dumb architecture decision that will keep haunting everyone who will have to work with this system later on. Your code is highly dependent on other schemas/databases, which is why you will have to implement quite a bit of error handling. Something you would not need to do, as the SQL compiler can tell you when you reach into nothingness. With dynamic SQL, that's happening at runtime.

    You also invite security issues of all sorts, from SQL injection to wrong configured roles and privileges - it's a sheer nightmare to correctly handle this for any non-trivial number of different schemas/databases (which really means any number >=2).

    In https://stackoverflow.com/a/47362689/4191767 I provided an alternative approach, which basically inverts the dependency of the schemas/databases and allowing for static, safe and easy to understand SQL code instead of the too-clever-for-your-own-good-dynamic-sql mess.

    If you want a sort of external (that is, from outside of the schema/database) access to your data, then that is something you should consider when building these schemas/databases.

    Alternatively, you are a programmer. What's stopping you from writing code to _create_ the code for each schema separately? All it takes is a template code and a replace function that puts the correct schema name in, where it's necessary.

    "What happens if a database gets added/dropped?" I hear you ask? Well, that would require another run of your generating code. And rightfully so, since you just took away a whole database, which is not a lightweight action.

    Alright, there you go. Two (three) approaches to deal with this architecture problem and no need for dynamic SQL. Life's good, ain't it?

    Add comment
    10|10000 characters needed characters exceeded