We help IT Professionals succeed at work.

Complex nested IF with EXISTS checks

dba123
dba123 asked
on
2,843 Views
Last Modified: 2012-05-05
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'.
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Author

Commented:
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]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
so, does that solve the problem or not?

Author

Commented:
not the adding of parense...so since I figured it out I'll PAQ on my answer then..
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.