Using If Else Statement Based on Count to Execute Different Insert Statements

Using IF ELSE statement based on Count to execute different Insert statements

Depending on your needs, here are a couple of ways:

IF EXISTS (SELECT * FROM TABLE WHERE COLUMN = 'SOME VALUE')
    --INSERT SOMETHING
ELSE
    --INSERT SOMETHING ELSE

Or a bit longer

DECLARE @retVal int

SELECT @retVal = COUNT(*) 
FROM TABLE
WHERE COLUMN = 'Some Value'

IF (@retVal > 0)
BEGIN
    --INSERT SOMETHING
END
ELSE
BEGIN
    --INSERT SOMETHING ELSE
END 

Sql If statement based on COUNT return

You can do the if without using a variable:

IF  (0 = (SELECT COUNT(field0)
         FROM tablex
         WHERE field6 is null AND field2 = @field2 AND field3 = @field3 AND field5 = @field5
        )
    )
begin
    exec cred.Demobilize(@field0, @field1);
end;

In practice, I think this would more commonly be written using not exists:

IF  (not exists (SELECT 1
                 FROM tablex
                 WHERE field6 is null AND field2 = @field2 AND field3 = @field3 AND field5 = @field5
                )
    )
begin
    exec cred.Demobilize(@field0, @field1);
end;

The not exists version can be faster, because the first row encountered will satisfy the condition clause. The count(*) version has to find all matching rows and count them.

IF Condition Perform Query, Else Perform Other Query Base on Count to Execute

In SQL Server CASE cannot be used for control flow. Use IF instead.

IF (SELECT count(*)
           ...) > 0
BEGIN
  SELECT ...
END
ELSE
BEGIN
  SELECT ...
END;

mysql query INSERT INTO in a conditional statements (case if/else)

Remember the difference between IF() function and IF statement.

One option you can implement is to use a FUNCTION (13.1.12 CREATE PROCEDURE and CREATE FUNCTION Syntax):

/* CODE FOR DEMONSTRATION PURPOSES */

DELIMITER //

CREATE FUNCTION `insert_new_value` (`p_foo` INT UNSIGNED) RETURNS TINYINT(1)
DETERMINISTIC
BEGIN
  /*
  INSERT INTO table1 (`id`, `column`) VALUE (1, 'a');
  SET @bar = SELECT LAST_INSERT_ID();
  -- UPDATE table2 SET c1 = @foo WHERE c2 = @bar;
  UPDATE table2 SET c1 = `p_foo` WHERE c2 = @bar;
  */
  INSERT INTO `ForgeRock`
  (`id`, `productName`, `description`)
  VALUES
  (4, 'a', 'b');
  RETURN 1;
END//

DELIMITER ;

SET @foo := 1;
-- You can also use CASE expression.
SELECT IF(1, `insert_new_value`(@foo), 0) INTO @`return_value`;

/* CODE FOR DEMONSTRATION PURPOSES */

SQL Fiddle demo

Nested If Statement with Insert Statements in SQL

From your description, it sounds like you would rather UPDATE a row than INSERT a row.

Try changing your inserts to this:

UPDATE dob.PMRInformation

SET = @AssignDate

WHERE PMRID = @PMRID

How to set multiple values inside an if else statement?

If you have more than one statement in a if condition, you must use the BEGIN ... END block to encapsulate them.

IF ((SELECT COUNT(*) FROM table WHERE table.Date > '2016-03-20') > 10)
BEGIN
 SET @test1 = 'test1'
 SET @test2 = 'test2' 
END
ELSE
BEGIN
 SET @test1 = 'testelse'
 SET @test2 = 'testelse'
END

SQL SELECT statement within an IF statement

The reason is that the Exists function is checking the result of the sub-query for existing - are there any rows or not. And, as you return the COUNT, it'll never be not-existing - COUNT returns 0 if there are no rows presented in database.

Try to store the resulting count in a local variable, like in this question:

Using IF ELSE statement based on Count to execute different Insert statements

DECLARE @retVal int

SELECT @retVal = COUNT(*) 
FROM TABLE
WHERE COLUMN = 'Some Value'

IF (@retVal > 0)
BEGIN
    --INSERT SOMETHING
END
ELSE
BEGIN
    --INSERT SOMETHING ELSE
END

---

Related Topics