February 8, 2013 at 10:21 am
BEGIN
DECLARE @FATHER AS INT
SET @FATHER = (SELECT FATHER FROM SEGMENT WHERE SID = @sid)
DECLARE @SON AS INT
SET @SON = (SELECT SON_NUMBER FROM SEGMENT WHERE SID = @sid)
DECLARE @RESULT AS INT
SET @RESULT = 0
DECLARE @TO_DELETE AS TABLE (ID INT)
INSERT INTO @TO_DELETE ( ID ) VALUES ( @sid )
DECLARE @CHILDREN AS TABLE (ID INT)
INSERT INTO @TO_DELETE ( ID ) VALUES ( @sid )
DECLARE @HOLD AS TABLE (ID INT)
DECLARE @HASCHILD AS INT
SET @HASCHILD = 1
BEGIN TRAN
BEGIN TRY
WHILE (@HASCHILD > 0)
BEGIN
/* add the children to the TO_DELETE Table */
INSERT INTO @TO_DELETE (ID)
( SELECT SID FROM SEGMENT WHERE SID IN (SELECT ID FROM @CHILDREN) )
/* add the children to the @HOLD Table */
INSERT INTO @HOLD (ID)
( SELECT SID FROM SEGMENT WHERE SID IN (SELECT ID FROM @CHILDREN) )
/* delete from the @CHILDREN Table */
DELETE FROM @CHILDREN
/* put the new children from the @HOLD table into the @CHILDREN Table */
INSERT INTO @CHILDREN (ID)
( SELECT ID FROM HOLD )
/* delete from the @HOLD table */
DELETE FROM @HOLD
/* get the children count */
SET @HASCHILD = ( SELECT COUNT(SID) FROM SEGMENT WHERE SID IN (SELECT ID FROM @CHILDREN) )
END
/* DELETE the SEGMENT_FACTS which are related to any TO_DELETE records */
DELETE FROM SEGMENT_FACTS WHERE SID IN ( SELECT ID FROM @TO_DELETE )
/* DELETE the SEGMENT records */
DELETE FROM SEGMENT WHERE SID IN ( SELECT ID FROM @TO_DELETE )
/* Update siblings */
UPDATE SEGMENT
SET SON_NUMBER = (SON_NUMBER - 1)
WHERE FATHER = @FATHER
AND SON_NUMBER > @SON
/* Return success */
SET @RESULT = 1
COMMIT TRAN
END TRY
BEGIN CATCH
/* Return failure */
SET @RESULT = 0
ROLLBACK TRAN
END CATCH
END
SELECT @RESULT
END
February 8, 2013 at 10:26 am
The message is fairly clear...
You have and END without a corresponding BEGIN. Format your code better and it becomes obvious:
BEGIN
DECLARE @FATHER AS INT
SET @FATHER = ( SELECT FATHER
FROM SEGMENT
WHERE SID = @sid
)
DECLARE @SON AS INT
SET @SON = ( SELECT SON_NUMBER
FROM SEGMENT
WHERE SID = @sid
)
DECLARE @RESULT AS INT
SET @RESULT = 0
DECLARE @TO_DELETE AS TABLE ( ID INT )
INSERT INTO @TO_DELETE
( ID )
VALUES ( @sid )
DECLARE @CHILDREN AS TABLE ( ID INT )
INSERT INTO @TO_DELETE
( ID )
VALUES ( @sid )
DECLARE @HOLD AS TABLE ( ID INT )
DECLARE @HASCHILD AS INT
SET @HASCHILD = 1
BEGIN TRAN
BEGIN TRY
WHILE ( @HASCHILD > 0 )
BEGIN
/* add the children to the TO_DELETE Table */
INSERT INTO @TO_DELETE
( ID
)
( SELECT SID
FROM SEGMENT
WHERE SID IN ( SELECT ID
FROM @CHILDREN )
)
/* add the children to the @HOLD Table */
INSERT INTO @HOLD
( ID
)
( SELECT SID
FROM SEGMENT
WHERE SID IN ( SELECT ID
FROM @CHILDREN )
)
/* delete from the @CHILDREN Table */
DELETE FROM @CHILDREN
/* put the new children from the @HOLD table into the @CHILDREN Table */
INSERT INTO @CHILDREN
( ID )
( SELECT ID
FROM HOLD
)
/* delete from the @HOLD table */
DELETE FROM @HOLD
/* get the children count */
SET @HASCHILD = ( SELECT COUNT(SID)
FROM SEGMENT
WHERE SID IN ( SELECT ID
FROM @CHILDREN )
)
END
/* DELETE the SEGMENT_FACTS which are related to any TO_DELETE records */
DELETE FROM SEGMENT_FACTS
WHERE SID IN ( SELECT ID
FROM @TO_DELETE )
/* DELETE the SEGMENT records */
DELETE FROM SEGMENT
WHERE SID IN ( SELECT ID
FROM @TO_DELETE )
/* Update siblings */
UPDATE SEGMENT
SET SON_NUMBER = ( SON_NUMBER - 1 )
WHERE FATHER = @FATHER
AND SON_NUMBER > @SON
/* Return success */
SET @RESULT = 1
COMMIT TRAN
END TRY
BEGIN CATCH
/* Return failure */
SET @RESULT = 0
ROLLBACK TRAN
END CATCH
END --This corresponds to your BEGIN
SELECT @RESULT
END --What does this match back to?
February 8, 2013 at 10:33 am
Thanks, It was formated before posting. I matched up the begin/ends a dozen times. Guess I had too much caffine. Thanks. Greatly appreciated.
February 8, 2013 at 10:52 am
This looks like it could use some rethinking. It looks like parent-child relationship or some sort. The logic is nearly impossible to follow but I think you could simplify this by using a recursive cte.
I think that what you have is the SEGMENT table and you want to delete the "FATHER" and subsequently delete all the children?
Does the following code get close the list of rows you want to delete? If it does, then you can just remove the select line and uncomment the delete.
;with cte as (
select SID
from SEGMENT
where SID = @sid
union all
select SID
from SEGMENT s
inner join cte on cte.FATHER = s.SID
)
select * from cte
--DELETE cte
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply