locked
Multiple IF ELSE Conditions RRS feed

  • Question

  • if exists(select * from AuthorityLimits where Description = 'Allow user to set min. image approval limit to release the batch' and Role = 'ANB QC Operator' and Text1 = '60' and status = '1') BEGIN Update AuthorityLimits set Role = 'ANB QC Operator',Status='1',Text1='60' where Description = 'Allow user to set min. image approval limit to release the batch' END 
    Else IF (select * from AuthorityLimits where Description = 'Allow user to set min. image approval limit to release the batch' and Role = 'ANB QC Operator' and status = '1') BEGIN Update AuthorityLimits set Role = 'ANB QC Operator',Text1='60' where Description = 'Allow user to set min. image approval limit to release the batch' END
    ELSE BEGIN insert into AuthorityLimits(Role,Description,Status,Text1) Values('ANB QC Operator','Allow user to set min. image approval limit to release the batch','1','60') END
    
    Msg 4145, Level 15, State 1, Line 2
    An expression of non-boolean type specified in a context where a condition is expected, near 'BEGIN'.
    Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'ELSE'.


    --Please assist to fix the above errors
    Friday, May 27, 2011 3:00 PM

Answers

  • I think the below tsql will solve your purpose -

    UPDATE AuthorityLimits 
    SET 
     [Role] = 'ANB QC Operator',
    	[Status]='1',
    	Text1='60' 
    WHERE 
     [Description] = 'Allow user to set min. image approval limit to release the batch' 
     
    IF @@ROWCOUNT = 0
     BEGIN
      INSERT INTO AuthorityLimits([Role],[Description],[Status],Text1) 
    		VALUES('ANB QC Operator','Allow user to set min. image approval limit to release the batch','1','60') 
     END 
    

    The basic reason I feel so is, I think your logic is to UPDATE the record if it exists else INSERT it.


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    • Proposed as answer by Naomi N Friday, May 27, 2011 4:34 PM
    • Marked as answer by KJian_ Thursday, June 2, 2011 7:54 AM
    Friday, May 27, 2011 4:00 PM

All replies

  • There was just a simple syntax error. Try this

    if exists(select * from AuthorityLimits 
    				where Description = 'Allow user to set min. image approval limit to release the batch' 
    				and Role = 'ANB QC Operator' 
    				and Text1 = '60' 
    				and status = '1') 
    BEGIN 
    Update AuthorityLimits 
    set Role = 'ANB QC Operator',
    	Status='1',
    	Text1='60' 
    where Description = 'Allow user to set min. image approval limit to release the batch' 
    END 
    Else 
    BEGIN
    	IF EXISTS (select * from AuthorityLimits 
    			where Description = 'Allow user to set min. image approval limit to release the batch' 
    					and Role = 'ANB QC Operator' 
    					and status = '1') 
    		BEGIN 
    			Update AuthorityLimits 
    			set Role = 'ANB QC Operator',
    				Text1='60' 
    			where Description = 'Allow user to set min. image approval limit to release the batch' 
    		END
    	ELSE 
    		BEGIN 
    			insert into AuthorityLimits(Role,Description,Status,Text1) 
    			Values('ANB QC Operator','Allow user to set min. image approval limit to release the batch','1','60') 
    		END
    END
    

    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    • Proposed as answer by Eric Isaacs Friday, May 27, 2011 3:15 PM
    Friday, May 27, 2011 3:04 PM
  • I think the below tsql will solve your purpose -

    UPDATE AuthorityLimits 
    SET 
     [Role] = 'ANB QC Operator',
    	[Status]='1',
    	Text1='60' 
    WHERE 
     [Description] = 'Allow user to set min. image approval limit to release the batch' 
     
    IF @@ROWCOUNT = 0
     BEGIN
      INSERT INTO AuthorityLimits([Role],[Description],[Status],Text1) 
    		VALUES('ANB QC Operator','Allow user to set min. image approval limit to release the batch','1','60') 
     END 
    

    The basic reason I feel so is, I think your logic is to UPDATE the record if it exists else INSERT it.


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    • Proposed as answer by Naomi N Friday, May 27, 2011 4:34 PM
    • Marked as answer by KJian_ Thursday, June 2, 2011 7:54 AM
    Friday, May 27, 2011 4:00 PM
  • In SQL 2008 use a single MERGE command - this is better for concurrency as indicated in Alex K blogs.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, May 27, 2011 4:07 PM
  • Very True. But don;t know the version of SQL Haris is using....so didn't proposed for the MERGE statement. However, I could have mentioned that too.

    Thanks for the addon tip !


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    Friday, May 27, 2011 4:16 PM