Stored Procedure - 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)

Stored Procedure

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2005-02-10 : 00:51:07

Dear all

I have 3 tables which are: tblUser, brgUserBudget, and tblBudget

tblUser has UserID, FirstName, LastNAme
brgUserBudget has UserID, BudgetID
tblBudget has BudgetID, Name

These 3 tables has a relationship. So in order to delete tblUser records I have to delete brgUserBudget first.

Normally I had to do in couple steps in the programming level and have 3 separate stored procedures like:

- Select UserID from brgUserBudget where BudgetID=??? and populated into string called: strUserIDS with comma delimited (have more than 1)
- Delete brgUserBudget where BudgetID=???
- DELETE tblUser where UserID IN (strUserIDS)

I would like to do this the same thing in one stored procedure (to make it simpler). So how do I do this?

Thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-10 : 01:23:54

A user can only have one budget link?

select UserID into #a from brgUserBudget where BudgetID = @BudgetID

delete brgUserBudget
where BudgetID = @BudgetID

delete tblUser
from tblUser u
join #a
on #a.UserID = u.UserID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dewacorp.alliances

452 Posts

Posted - 2005-02-10 : 19:18:17

Hi Nr

Is there anyway to avoid using temp table? May be the derived tables instead or something?

Thanks

quote:
Originally posted by nr

A user can only have one budget link?

select UserID into #a from brgUserBudget where BudgetID = @BudgetID

delete brgUserBudget
where BudgetID = @BudgetID

delete tblUser
from tblUser u
join #a
on #a.UserID = u.UserID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-10 : 19:34:17

delete brgUserBudget
where BudgetID = @BudgetID

delete tblUser
from tblUser u
join (select UserID from brgUserBudget where BudgetID = @BudgetID) t
on t.UserID = u.UserID

It's the same difference though.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources