If you run the following in sql server...
CREATE SCHEMA [cp]
GO
CREATE TABLE [cp].[TestIt](
[ID] [int] NULL
) ON [PRIMARY]
GO
CREATE PROCEDURE cp.ProcSub
AS
BEGIN
Print 'Proc Sub'
END
GO
CREATE PROCEDURE cp.ProcMain
AS
BEGIN
Print 'Proc Main'
EXEC ProcSub
END
GO
CREATE PROCEDURE cp.ProcMain2
AS
BEGIN
Print 'Proc Main2'
SELECT * FROM TestIt
END
GO
exec cp.ProcMain2
GO
exec cp.ProcMain
GO
You get the error
Could not find stored procedure 'ProcSub'
Which makes it impossible to compartmentalize procedures into a schema without hard coding the schema into the execution call. Is this by design or a bug as doing a select on tables looks in the procedures schema first.
If anyone has a work around I'd be interested to hear it, although the idea is that I can give a developer two stored procedures which call each other and can put into whatever schema they like in 'their' database that they can run for the purpose of being a utility that looks at the objects of another given schema in the same database.
I have looked to see if I might be able to get round it with Synonyms but they seem to have the same problem associated with them.