December 22, 2013 at 12:28 am
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(param1param2,param3);
}
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
December 22, 2013 at 5:36 am
you can check for foreign key constraints using this query-
UPDATE
SET A.deleted = 1
FROM tblParent A
LEFT JOIN tblChild B ON A.PID = B.PID
WHERE B.PID IS NULL
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 22, 2013 at 6:19 am
Thank you, but mentioning the childtable explicitly is not an option here. A parent table can have many child tables.
December 22, 2013 at 2:30 pm
sharpcnet (12/22/2013)
Thank you, but mentioning the childtable explicitly is not an option here. A parent table can have many child tables.
That's a major design problem that probably should have been avoided. :pinch:
The answer to your question is to take advantage of what sp_ExecuteSQL is actually capable of... passing parameters.
I can't test this because I don't have your insitu data or tables, but something like the following should fix you right up. Look for "changed here" to see the changes I made to your code.
ALTER PROCEDURE SoftDelete
(
@TableName nvarchar(50), @ColName nvarchar(50),
@Id nvarchar(50),
@Return TINYINT = NULL OUTPUT --Changed here
)
AS
BEGIN
DECLARE @qry nvarchar(500)
SELECT @qry = 'begin transaction
delete '+@tablename+' where '+@colname+'='+@id+'
if(@@Error <> 0)
Begin
SELECT @Return = 0 --Changed here
End
else
Begin
rollback transaction
update '+@tablename+' set deleted = 1 where '+@colname+' = '+@id+'
SELECT @Return = 1 -Changed here
end'
EXECUTE sp_executesql @qry,
,N'@Return TINYINT OUTPUT' --Defines the passed parameter -- Changed here
,@Return = @Return OUTPUT --Returns the value of the parameter -- Changed here
END
Shifting gears a bit, this code is extremely prone to SQL Injection and a security accident just waiting to happen. You [font="Arial Black"]really [/font]need to santize your inputs on this code to make sure that there is no SQL Injection.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply