asked on

Complex nested IF with EXISTS checks

My head is spinning

BEGIN
SET @str = substring(@tmpstr, 1, @pos - 1)
                  BEGIN
                        IF (NOT EXISTS (Select 1 from ProductRelationship where ProductID = @ProductID or RelatedProductID = @ProductID))
                        INSERT INTO ProductRelationship VALUES(@ProductID, convert(bigint, @str), 2,@ProductDescription, GetDate(), 2, NULL, NULL, 1, 0)
                        ELSE IF (EXISTS(Select 1 from ProductRelationship where RelatedProductID = @ProductID)
                                                AND      NOT EXISTS (Select 1 from ProductRelationship where ProductID = @ProductID)
                        INSERT INTO ProductRelationship VALUES(@ProductID, convert(bigint, @str), 2,@ProductDescription, GetDate(), 2, NULL, NULL, 1, 0)
                        ELSE IF (EXISTS (Select 1 from ProductRelationship where ProductID = @ProductID)
                        INSERT INTO ProductRelationship VALUES(convert(bigint, @str), @ProductID, 3,@ProductDescription, GetDate(), 2, NULL, NULL, 1, 0)
                  END
                  SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
SET @pos = charindex(',', @tmpstr)
END

Msg 156, Level 15, State 1, Procedure Insert_Product_Child_Relationships, Line 42
Incorrect syntax near the keyword 'INSERT'.
Msg 156, Level 15, State 1, Procedure Insert_Product_Child_Relationships, Line 43
Incorrect syntax near the keyword 'ELSE'.
Msg 156, Level 15, State 1, Procedure Insert_Product_Child_Relationships, Line 44
Incorrect syntax near the keyword 'INSERT'.

Guy Hengel [angelIII / a3]

please try with an additional () around the 2 conditions:

WHILE @pos > 0
BEGIN
SET @str = substring(@tmpstr, 1, @pos - 1)
BEGIN
IF (NOT EXISTS (Select 1 from ProductRelationship where ProductID = @ProductID or RelatedProductID = @ProductID))
INSERT INTO ProductRelationship VALUES(@ProductID, convert(bigint, @str), 2,@ProductDescription, GetDate(), 2, NULL, NULL, 1, 0)
ELSE IF ( EXISTS ((Select 1 from ProductRelationship where RelatedProductID = @ProductID)
AND NOT EXISTS (Select 1 from ProductRelationship where ProductID = @ProductID))
)
ELSE IF (EXISTS (Select 1 from ProductRelationship where ProductID = @ProductID)
INSERT INTO ProductRelationship VALUES(convert(bigint, @str), @ProductID, 3,@ProductDescription, GetDate(), 2, NULL, NULL, 1, 0)
END
SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
SET @pos = charindex(',', @tmpstr)
END

dba123

ASKER

here we go

WHILE @pos > 0
BEGIN
SET @str = substring(@tmpstr, 1, @pos - 1)
BEGIN
IF (NOT EXISTS (Select 1 from ProductRelationship where ProductID = @ProductID or RelatedProductID = @ProductID))
INSERT INTO ProductRelationship VALUES(@ProductID, convert(bigint, @str), 2,@ProductDescription, GetDate(), 2, NULL, NULL, 1, 0)
ELSE IF (EXISTS(Select 1 from ProductRelationship where RelatedProductID = @ProductID)
AND NOT EXISTS (Select 1 from ProductRelationship where ProductID = @ProductID))
INSERT INTO ProductRelationship VALUES(convert(bigint, @str), @ProductID, 3,@ProductDescription, GetDate(), 2, NULL, NULL, 1, 0)
ELSE IF EXISTS (Select 1 from ProductRelationship where ProductID = @ProductID)
INSERT INTO ProductRelationship VALUES(convert(bigint, @str), @ProductID, 2,@ProductDescription, GetDate(), 2, NULL, NULL, 1, 0)
END
SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
SET @pos = charindex(',', @tmpstr)
END

Guy Hengel [angelIII / a3]

so, does that solve the problem or not?

dba123

ASKER

not the adding of parense...so since I figured it out I'll PAQ on my answer then..

ASKER CERTIFIED SOLUTION

DarthMod

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial