MSSQL function call without specifying a schema

Question

In my Java program, I need to call a stored procedure called FooBar(null) . The program tries to be independent of the db platform. Some SQL statements are combined programmatically, one of them:

 insert into foo_bar (id, user, timestmp) values (1, 'foo', FooBar(null)); 

It works on Oracle, HSLQDB, MySQL. Now I have to get it working on MSSQL. I created a FooBar function. I enter the database with a user who has the default schema foo .

This statement only works if I put the schema name in front of the function name:

 insert into foo_bar (id, user, timestmp) values (1, 'foo', foo.FooBar(null)); 

If I write to the database, then exec FooBar(null) works without foo. You know how to avoid this foo. in MSSQL?

Answer 1

Unfortunately, unlike almost all other objects, DB functions must be fully qualified with the schema name in T-SQL.

If you put the expression together programmatically, you can crack it to see if the JDBC driver is MSSQL and in this case add the schema name.

Answer 2

You can try a synonym , for example

 CREATE SYNOMYM FooBar FOR foo.FooBar; 

If this fails, you're out of luck ...