How To Return Value From Dynamic SQL

Posted by Sharpcnet under Sql Server on 12/22/2013 | Points: 10 | Views : 744 | Status : [Member] | Replies : 3
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 a value 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:

C#:
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
ERROR: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1




Responses

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

Up
0
Down
Refer the below script...
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


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

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

Up
0
Down
Refer
http://forums.asp.net/t/1776661.aspx
http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx

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

Posted by: Bandi on: 1/3/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
"Mark it as Answer "

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