Sql query problem - 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

General SQL Server Forums

New to SQL Server Programming

Sql query problem

Author

Topic

anishmca2008
Starting Member

2 Posts

Posted - 2009-08-12 : 05:19:11

Hi friends..
Iam having the query below.

CREATE PROCEDURE [dbo].[sp_deletestudent]
(
@reg_sid int,
@fee_studid int,
@ins_feeid int,
@feeid int
@m_studid int,
@e_studid int
)
AS
begin transaction
delete from tbl_reg where reg_sid=@reg_sid
delete from tbl_fees where fee_studid= @fee_studid
delete from tbl_ins where ins_feeid= @ins_feeid
delete from tbl_sto where feeid= @feeid
delete from tbl_mark where m_studid=@m_studid
delete from tbl_exam where e_studid=@e_studid
if @@error=0
commit
else
rollback
GO

Iam deleteting six tables from this stored procedure.
SO only if the ID present in all these tables this procedure will work.In some situtation tbl_mark and tbl_exam wont have the id.
At that situtation the query is failing.
But i want it to work.
How come its possible.
Please modify the query .
thanks in advance.

By
Anish
You must be capable of changing and rearranging your priorities so that your final goal can be achieved.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-12 : 05:27:38

quote:
Originally posted by anishmca2008

Hi friends..
Iam having the query below.

CREATE PROCEDURE [dbo].[sp_deletestudent]
(
@reg_sid int,
@fee_studid int,
@ins_feeid int,
@feeid int
@m_studid int = null,
@e_studid int = null
)
AS
begin transaction
delete from tbl_reg where reg_sid=@reg_sid
delete from tbl_fees where fee_studid= @fee_studid
delete from tbl_ins where ins_feeid= @ins_feeid
delete from tbl_sto where feeid= @feeid
if (@m_studid is not null)
delete from tbl_mark where m_studid=@m_studid
if(@e_studid is not null )
delete from tbl_exam where e_studid=@e_studid

if @@error=0
commit
else
rollback
GO

Iam deleteting six tables from this stored procedure.
SO only if the ID present in all these tables this procedure will work.In some situtation tbl_mark and tbl_exam wont have the id.
At that situtation the query is failing.
But i want it to work.
How come its possible.
Please modify the query .
thanks in advance.

By
Anish
You must be capable of changing and rearranging your priorities so that your final goal can be achieved.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-12 : 06:16:30

quote:

Iam deleteting six tables from this stored procedure.
SO only if the ID present in all these tables this procedure will work.In some situtation tbl_mark and tbl_exam wont have the id.
At that situtation the query is failing.

What do you mean the query is failing?

If there is nothing to delete (no rows satisfying "delete from tbl_exam where e_studid=@e_studid") then that's not a failure is it?

Then how does that possibly fail?

Are you getting some sort of error?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-12 : 06:50:05

Also you are only checking @@error at the end of 6 different delete statements

this will only trap a problem with the LAST delete statement. If there is some other error in say delete statement 3 then you will blithely commit the transaction anyway.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources