Dependency of Stored Procedure

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 2000 Forums

SQL Server Development (2000)

Dependency of Stored Procedure

Author

Topic

joriveek
Starting Member

33 Posts

Posted - 2006-02-23 : 06:48:53

Hi Forum....

How can I create/add a stored procedure dependent on a Table?
So that when I see 'View Dependencies' of the table, I can see this SP dependent on that Table...

Thanks
J.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-23 : 07:47:04

Run this

sp_depends 'tableName'

Madhivanan

Failing to plan is Planning to fail

joriveek
Starting Member

33 Posts

Posted - 2006-02-23 : 08:50:05

I tried:

my_stored_procedure my_table

but got error converting data type nvarchar to int,

as it I am casting my_table as my_stored_procedure.

Didn't work it seems to be....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-23 : 08:52:30

Did you try my suggestion?

Post the exact code you used

Madhivanan

Failing to plan is Planning to fail

joriveek
Starting Member

33 Posts

Posted - 2006-02-23 : 09:15:01

Exact Code is:

dbo.createRefundType dbo.PSRefundType

where dbo.createRefundType is the stored procedure, executed and works fine when I call this SP.

dbo.PSRefundType is the table I operate the above stored Procedure. Basically the above stored procedure depends on the PSRefundType table. I want to create the dependency of the same.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-23 : 09:28:19

dbo.createRefundType dbo.PSRefundType is equivalent to executing the sp by passing the value dbo.PSRefundType. If you want to see the dependencies of table use this

sp_depends 'dbo.PSRefundType'

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-02-23 : 09:55:49

Note that sp_depends is not very reliable ... so you will need care if you need to rely on this 100% rather than just using it in addition to other means.

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61534 for a test I made showing how sp_depends can get out of shape

Kristen

joriveek
Starting Member

33 Posts

Posted - 2006-02-23 : 11:04:20

Sorry, my actual question is that 'How do I CREATE dependency between table and stored procedure?' Is there any straight answer?

It is just to make sure that the stored procedure cannot work without the table it depends on.

Thanks for the answer,

Kristen
Test

22859 Posts

Posted - 2006-02-23 : 14:20:33

You can check that the table exists at runtime (i.e. within the code for the Sproc)


IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'MyTableName' and type = 'U')
BEGIN
	... code dependent on MyTableName ...
END

Note: You probably ought to use INFORMATION_SCHEMA.TABLES instead of sysobjects

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-24 : 02:35:27

There is chance that you can drop the table without dropping the dependent objects. In that case running sp will lead to error. I know how to make sure integrity between table and View

Create View yourView with Schemabinding
as
Select columns from owner.tableName

Now you cant drop the table until you drop the view referencing it

But I dont know how to simulate the same for stored Procedure

Madhivanan

Failing to plan is Planning to fail

joriveek
Starting Member

33 Posts

Posted - 2006-02-24 : 10:21:21

.....but there is a way to make stored procedure dependent on a table instead of doing at run time, we can do this in Visual DB tools and catch the exception in case user trying to delete a table and making the stored procedure orphan.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-24 : 10:40:46

If you dont want to drop the table if it is referenced by other objects, see if this helps

Create table #t(procedureName varchar(100),type varchar(40))

Insert into #t EXEC sp_depends 'tt'
if exists(select * from #t)
--table is referenced
else
--drop table

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-24 : 10:52:03

I believe the answer is "no". I don't think there is an "out of the box" way to be prevented from dropping a table simply because it id being referenced by a stored procedure. You'll need to safegaurd against that through other means.

sysdepends (sp_depends) and syscomments wouldn't be a (100%) reliable source because of the reason Kristen mentioned plus they won't track accross all databases

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-24 : 10:54:34

Why is no schemabining option to sp as that of View?

Create View yourView with Schemabinding
as
Select columns from owner.tableName

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources