SQl Transactions - query doesnt return value as expected

Posted by Sharpcnet under Sql Server on 12/21/2013 | Points: 10 | Views : 1647 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

0	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

0	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

0	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

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

Latest Posts