Add If Statement To Stored Procedure I am running an Insert Into statement in my stored procedure that is receiving data from a Table Value Parameter  I just realized I need to 1st verify that the data does not already exist in the table before doing an insert so I am attempting to alter my procedure to this

ALTER PROC [dbo].[CMT]
(@TempTable AS dbo.TableValue_ET READONLY)
AS
BEGIN

if b.[empID] NOT IN (Select [empID] FROM [Habib].[dbo].[SE])
BEGIN
  INSERT INTO [Habib].[dbo].[SE]([empID],[POD],[empNotes],[hiredate],[terminationdate],[terminationtime],[address],[city],[state])
  SELECT
  [empID]
  ,[POD]
  ,[empNotes]
  ,convert(varchar(10), [b].[hiredate], 101)
  ,convert(varchar(10), [b].[terminationdate], 101)
  ,[terminationtime] = LTRIM(RIGHT(CONVERT(VARCHAR(20), [b].[terminationdate], 100), 7))
  ,[address]
  ,[city]
  ,[state]
  FROM @TempTable b
END

END


However, when I attempt to save this updated syntax I get an error of


  Msg 4104, Level 16, State 1, Procedure UpdateShippingExceptions, Line 7
  The multi-part identifier "b.empID" could not be bound.


What must I change so that I can 1st check if the empID already exists in the table before inserting it?
The basic form is:

INSERT dbo.table
(
  cols
)
SELECT 
  cols
FROM @tvp AS t
WHERE NOT EXISTS 
(
  SELECT 1 FROM dbo.basetable AS b WHERE b.key = t.key
);


In your case, that would be:

INSERT INTO [Habib].[dbo].[SE]
(
  [empID],
  [POD],
  [empNotes],
  [hiredate],
  [terminationdate],
  [terminationtime],
  [address],
  [city],
  [state] /* why do people prefer horizontal scrollbars to vertical ones? */
)
SELECT 
  [empID],
  [POD],
  [empNotes],
  convert(char(10), [b].[hiredate], 101),
  convert(char(10), [b].[terminationdate], 101),
  LTRIM(RIGHT(CONVERT(VARCHAR(20), [b].[terminationdate], 100), 7)),
  [address],
  [city],
  [state]
FROM @TempTable AS b
WHERE NOT EXISTS
(
  SELECT 1 
    FROM [Habib].[dbo].[SE] AS se
    WHERE se.empID = b.empID
);


One thing you need to find out, though, is if users enter existing empID values here and expect to update existing employees with new information for the other columns... if so, you're throwing their updates away without telling them.