Should I or shouldn't I? - SQL Server Forums

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

Transact-SQL (2000)

Should I or shouldn't I?

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-09-29 : 11:54:51

I have a situation where I have two 'objects' in tables that I know are related and I want to delete all of them by being given information to identify one object. So for example, let's say I have two tables, people and things

Table PEOPLE

People_ID , ...

Table THINGS

Thing_ID , People_ID,... etc

What I was thinking was that I could have a single proc that is passed a parameter that is either a People_ID or a Thing_ID (and hence some what if knowing which it was) e.g.
CREATE PROCEDURE MyProc
@People_ID int = <Default>,
@Thing_ID int = <Default>
AS
If People_ID <> Default ... -- do stuff if people ID is passed
ELSE
-- do stuff if Thing_ID is passed

But now I'm wondering if this is just making things complicated. Would I be better with two seperate sprocs?

I'd be interested in hearing what people think

thanks

steve

Steve no function beer well without

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-29 : 12:12:22

IMHO most simple in one sproc

delete People
from People P
inner join Things T on P.PeopleId = T.PeopleId
where (PeopleId = @PeopleId or @PeopleId is null) and
(ThingId = @ThingId or @ThingId is null)

delete Things
from People P
inner join Things T on P.PeopleId = T.PeopleId
where (PeopleId = @PeopleId or @PeopleId is null) and
(ThingId = @ThingId or @ThingId is null)

EDIT:
you can use 2 sproc, but you have to call them in right order. i prefer 2 sproc way
Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2004-10-11 : 07:00:17

The optimiser might make a better job with 2 SProcs. Under SQL7 (I think) the first run will create the cached query plan, so the fact that only one of the DELETEs will actually do anything will cause the query plan to be pants for the alternative usage. But there again it could be my memory thats pants ...

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-11 : 07:11:42

I would use 2 separate sprocs, faster and easier.
Using 1 proc would, as you say just complicate things.
IMO
/rockmoose

Kristen
Test

22859 Posts

Posted - 2004-10-11 : 07:25:13

Hang on, I've had a better thought.

Set a trigger on People so that it pre-deletes all Things.

That way users don't have to bother to delete Things at all ... saves time, trouble, and development, testing, QA, rollout, backup, infrastructure, backup, redundacy and backup.

This is something you are building for your NetAdmin group isn't it?!

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-11 : 08:13:35

CREATE TABLE THINGS( ..., People_ID INT NOT NULL REFERENCES dbo.People(People_ID) ON DELETE CASCADE, ... ) ?

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources