Checking objects before compiling Stored Proc

[MastermindSQL]

Could someone suggest me a way to check the underlying objects while compiling a stored procedure? For example if I have a code like this:
---------------------------
CREATE PROC dbo.FOO
AS
SELECT Col1, Col2
FROM dbo.NonExistingTable

RETURN
GO
---------------------------
In this case the [NonExistingTable] does not exist so I want to make compilation of this proc fail. Thank you in advance.

 

[philhege]

How about

IF EXISTS(SELECT table_name FROM INFORMATION_SCHEMA.tables
WHERE table_name = 'dbo.NonExistingTable')
BEGIN
CREATE PROC dbo.FOO
AS
SELECT Col1, Col2
FROM dbo.NonExistingTable

RETURN
END
GO

as an example.

If you want to parse submitted code dynamically for object existence, well, have fun writing the parse code.
I have my developers submit code for all objects, and test it thoroughly, before it can see the faint green power light of the production server.


Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.