Issue with If Condition while inserting into table

• mcfarlandparkway

  SSCertifiable

  Points: 7699

  More actions

  February 23, 2017 at 8:14 am

  #320671

  I have some values where I am inserting into table
  @Id int
  @IsEmp varchar(1)
  Insert into table1
  (col1,
  col2,
  col3)
  values
  (@col1,
  @col2,
  -1 )

  In col3 we are passing value as -1

  I have an if condition where I am trying to place it in place of -1 n the values section. while inserting into table for col3 I have pass this if condition in values.how to replace - value with the total if condition?

  If @IsEmp = 'N'
  BEGIN
  SET @col3 = -1
  END
  ELSE
  SET @col3 = @Id
  

• drew.allen

  SSC Guru

  Points: 76944

  More actions

  February 23, 2017 at 8:46 am

  #1929965

  You need to use a CASE expression.  Using an IF inside of an INSERT/VALUES statement could potentially violate the atomicity of that statement.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• mcfarlandparkway

  SSCertifiable

  Points: 7699

  More actions

  February 23, 2017 at 8:52 am

  #1929967

  This is an example of case statement

   Case When @Company = '' then 'no company' Else 'my company' End

  In my case I am checking the If condition with one variables and assigning the values to another variable.

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  February 23, 2017 at 11:51 am

  #1930023

  Here's the how on implementing the CASE statement:

  
DECLARE @col1 AS int = 255;

  DECLARE @col2 AS char(1) = 'X';
DECLARE @IsEmp AS varchar(1) = 'N';
DECLARE @Id AS int = 1;

  INSERT INTO table1 (col1, col2, col3)
    VALUES    (@col1, @col2, CASE @IsEmp WHEN 'N' THEN -1 ELSE @Id END);


  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)