Access Functions (UDF)

Question

In "Shared Database, Separate Schemas" the tables and stored procedures and functions are created using db scripts.

And the tables and stored procedure are access as follows (the user is logged for the schema which he has rights)

select * from table_name

exec stored_procedure_name

But how to access functions??

Because function always needs schema_name for e.g: select dbo.GetEmpName();

Any suggestion????

Answer 1

Because function always needs schema_name for e.g: select dbo.GetEmpName();

Yes, you have to address the schema with functions, so what's the problem?

BTW, even for table / stored procedure you should add the schema name.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

If the user logs in he can access to the schema to which the user has rights.
And the also it is not necessary to use schema name for the tables which user has rights.
Means user can access the table as select * from table_name instead of select * from schema.table_name.

My main concerned is when we use "Shared Database, Separate Schemas" in sql azure for multi-tenancy the tables and stored procedure can be accessed without schema name.

The schema along with the data objects(tables,stored procedure) are created dynamically.

The problem is that how to access the functions (because the functions needs to be accessed with schema name).
And this functions are called from stored procedure (currently it is called as select dbo.function_name).

Is there any way we can call the functions without schema name???

Does "Shared Database, Separate Schemas" in sql azure supports user defined functions???

Any suggestion????

Answer 3

Any suggestion...from anyone...

Answer 4

That's the restrition for UDF, if you use it in a select statement, you have to add the schema name.

If you call the UDF with a EXECUTE then you don't need the schema name (sample for AdventureWorks)

-- This works without schema name
EXEC [ufnGetStock] @ProductID = 1
GO

-- This don't work
SELECT [ufnGetStock](1)
GO

-- You have to use schema name
SELECT dbo.[ufnGetStock](1)

---

Olaf Helper
Blog Xing

Answer 5

Hi - Eventhough the schema is constructed at runtime, you should be able to prefix it with database owner scheme (dbo) and execute the functions. I am assuming along with creating runtime tables and SP's you are also creating functions at runtime.

---

MkMahesh