How to avoid creating a SP with errors

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

How to avoid creating a SP with errors

Author

Topic

kirederf
Starting Member

3 Posts

Posted - 2009-05-07 : 10:36:42

Hi, I wasn't sure where to post this.

I was wondering if there was a way to avoid having a stored procedure compile correctly if there is a reference to a table or view that doesn't exist.

For example


CREATE Procedure [dbo].[RandomStoredProcedure]
AS
SELECT *
FROM A_TABLE_THAT_DOES_NOT_EXIST

will give the following output

Command(s) completed successfully.

A solution to this might avoid us problems in the future, thanx.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-05-07 : 17:25:47

add WITH SCHEMABINDING to your stored procedures will help around the deferred name resolution
Consider the implications of this though! Drop/re-create a table with lots of associated procedures could be painful.

kirederf
Starting Member

3 Posts

Posted - 2009-05-08 : 09:51:05

Thanx a lot buddy.

So basically, the WITH SCEMABINDING could be added to the creation / alteration of the store procedure until it reaches its final state, then could be altered without the WITH SCEMABINDING to avoid future pain? :)

kirederf
Starting Member

3 Posts

Posted - 2009-05-08 : 09:52:18

MMMMM

but is there a way to add this as a default setting on the server to avoid having to add it each time a procedure or view or function is created?

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-05-08 : 15:17:17

Apologizes. Can't use SCHEMA_BINDING with stored procedures. I've used it with functions and just assumed :-(
See this: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124490

Maybe this will help instead, form BOL:
"sp_refreshsqlmodule should be run when changes are made to the objects underlying the stored procedure, user-defined function, or view that affect its definition. Otherwise, the object might produce unexpected results when it is queried or invoked.
To refresh a view, you can use either sp_refreshsqlmodule or sp_refreshview with the same results."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources