Can we create procedure when the referring objects such as views or tables do not exist in the database

Yes, SQL Server allows creation of stored procedure even if the objects that it refers to do not exist in the database.

Following is an example:

CREATE PROCEDURE  SP_ProcedureWithoutTable

AS

BEGIN

SELECT * FROM NonExistentTable

END

Execute it and you get the message

Command(s) completed successfully.

NonExistentTable (which could have been a view or table) did not exist in the database. It did not bother to check whether xyz existed in the database or not and simply created the procedure.

Now when you try to execute the procedure:

EXEC SP_ProcedureWithoutTable

It gives the following error:

Msg 208, Level 16, State 1, Procedure SP_ProcedureWithoutTable, Line 4

Invalid object name ‘NonExistentTable'.

The reason is SQL Server follows deferred name resolution. This means you can create stored procedures referring to tables or views that do not exist yet. The stored procedure is compiled the first time it is used. During compilation it tries to resolve all the names of the objects and that is the time, it will fail if the table/view name does not exist.