sql - Reference a table from a scalar UDF - tied to schema name? -

due rollout/architecture of our system, create scalar udf use within select statement in stored procedure

because our various dev/test/live environments have different schema names, ideally name udf dbo.myfunctionname, however, because function contains logic uses select statement values database table return answer means cannot use dbo must put under schema of database (ie: test) note - right, right?

when calling function select statement, must provide two-part name, must use test.myfunctionname

select name, age, test.myfunctionname(personid) people 

is there way parameterise schema name when roll out different schema, not need create function under each 1 and/or amend how called? ideally, i'm looking

select name, age, schema_name().myfunctionname(personid) people 

but i'd not use dynamic sql if possible

many thanks

your question 2 questions:

• does function need have same schema tables it's selecting from? answer no. dbo.myfunction() can select test.mytable fine. however, may run issues ownership chaining -- different schemas mean explicit permissions must exist user select table. see books online details.
• can invoke function parameterized schema, without using dynamic sql? answer no. object name static -- although sql have "feature" in form of deferred name resolution allows refer objects in stored procedures if not exist, still doesn't allow make schema name variable.