SQl Transactions - query doesnt return value as expected

Posted by Sharpcnet under Sql Server on 12/21/2013 | Points: 10 | Views : 818 | Status : [Member] | Replies : 5
tblParent
pid(int) name deleted(bit)
1 abc 0
2 def 0
tblChild
cid(int) name  pid(ForeignKey)
1 aaa 1
2 bbb 1
When a record from `tblParent` is being deleted, it should check for any child records. If yes, rollback & return 0. If no, then update the deleted column to '1' and return 1. Basically, doing a soft delete

The SP works fine. All I need is to know the status as 0 or 1 based upon the action that took place. How should it be done. I would call this store procedure from c#, linq to entities to get the status. something like:
public int somefuntion() //returning a string is also fine..
{
return MYDB.SoftDelete(parameters.....);
}
ALTER PROCEDURE SoftDelete
(
@TableName nvarchar(50), @ColName nvarchar(50),
@Id nvarchar(50)
)
AS
BEGIN
DECLARE @qry nvarchar(500)

SELECT @qry = 'begin transaction
delete '+@tablename+' where '+@colname+'='+@id+'
if(@@Error <> 0)
Begin
--select 0
End
else
Begin
rollback transaction
update '+@tablename+' set deleted = 1 where '+@colname+' = '+@id+'
--select = 1
end'

EXECUTE sp_executesql @qry
END





Responses

Posted by: vishalneeraj-24503 on: 12/21/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi, there is a mistakes in your delete statement,from is missing in your delete query,please write your query as

delete from  '+@tablename+' where '+@colname+'='+@id+';


Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sharpcnet on: 12/21/2013 [Member] Starter | Points: 25

Up
0
Down
Thats ok, cos it works either ways.

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: vishalneeraj-24503 on: 12/21/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi Sharpcnet,please mark it if it solves your problem.

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sharpcnet on: 12/21/2013 [Member] Starter | Points: 25

Up
0
Down
No It did not. The problem is not with the delete syntax -
Delete will work - "delete tbl1 where id =1" and also "delete from tbl1 where id=1".

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 12/23/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
create table tblParent (pid int primary key, name varchar(5),  deleted bit )
insert tblParent
SELECT 1, 'abc', 0 union all
SELECT 2, 'def', 0

create table tblChild (cid int primary key, name varchar(5), pid int references tblParent(pid))
insert tblChild
sELECT 1, 'aaa', 1 union all
sELECT 2, 'bbb' , 1
GO
ALTER PROCEDURE SoftDelete

(

@TableName nvarchar(50), @ColName nvarchar(50),

@Id nvarchar(50)

)

AS

BEGIN

DECLARE @qry nvarchar(500)



SELECT @qry = 'begin transaction

delete '+@tablename+' where '+@colname+'='+@id+'

if(@@Error <> 0)

Begin

select 0 AS Status
Commit transaction

End

else

Begin

rollback transaction;

update '+@tablename+' set deleted = 1 where '+@colname+' = '+@id+'

select 1 AS Status

end
'

EXECUTE sp_executesql @qry

END

exec SoftDelete 'tblParent', 'pid', 1 -- Status = 0

exec SoftDelete 'tblParent', 'pid', 2 -- Status = 1

--DROP TABLE tblChild
--DROP TABLE tblParent
--DROP PROCEDURE SoftDelete


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response