sql server - How to run a schema proc from another schema -

let's in multi-schema db have these 2 procs:

create proc s1004.proc1    exec proc2 go create proc s1004.proc2     select 1 

then, when try run proc1 sa login, sql issues error : not find stored procedure 'proc2'.

i know if add schema proc2 in body of proc1, can resolve schema.

is there other solution problem.

if can use schema in 'scope' of user (be aware users , schemas separated in sql server), away this:

create user s1004 login s1004 default_schema = s1004; go  create procedure s1004.proc1  execute 's1004'   execute proc2 go  create procedure s1004.proc2     select 1  exec s1004.proc1  

what happens here create user s1004 default schema same name. schema searched object when object not found in scope of current schema.

when need resolve schema in procedure, run procedure in context of user (see with execute 's1004') , schema resolution succeed.

however, pay attention changes execution scope user. it's workaround, under circumstances it's best can get.