I've got an SQL statement that creates an appointment when a field become populated. The good news is that it works, and the people who have an ID and will always be a part of the appointment. The other good news is that in the opportunity the oppo_salesperson field is required, so this, too, will always be populated. The catch is that there are two other fields "Engineer Salesperson" and "EPI Salesperson" that are not required, but when they are, it's important to include them in this appointment. Simply by putting out a DECLARE and then SET = #oppo_engsalesperson# works if there's something in that field to grab. However, in the case when there is no one selected, it spits out an SQL error.

Is there an IF statement or some other way for it to be skipped if it returns NULL?
I'm thinking like, SET @CMLI_EngSalesPerson = (SELECT oppo_engsalesperson FROM Opportunity WHERE oppo_engsalesperson IS NOT NULL)
and even if it does work at that spot, then at the bottom where it inserts @CMLI_EngSalesPerson into the Comm_Link table it'll still try to pass something that doesn't exist?

DECLARE @Comm_CommId INT
DECLARE @Comm_OppoId INT
DECLARE @Comm_DateTime DATETIME
DECLARE @Comm_ToDateTime DATETIME
DECLARE @Comm_Note NVARCHAR(50)
DECLARE @Comm_CreatedBy INT
DECLARE @Comm_CreatedDate DATETIME
DECLARE @Comm_TimeStamp DATETIME
DECLARE @Comm_SecTerr INT
DECLARE @Comm_Subject NVARCHAR(50)
DECLARE @Comm_UpdatedBy INT
DECLARE @Comm_UpdatedDate DATETIME

DECLARE @CMLI_CommLinkId INT
DECLARE @CMLI_UserId INT
DECLARE @CMLI_CommId INT
DECLARE @CMLI_CreatedBy INT
DECLARE @CMLI_CreatedDate DATETIME
DECLARE @CMLI_TimeStamp DATETIME
DECLARE @CMLI_CommPersonId INT
DECLARE @CMLI_CommCompanyId INT
DECLARE @CMLI_SalesPerson INT

SET @Comm_OppoId = #Oppo_OpportunityId#
SET @Comm_DateTime = (SELECT Oppo_biddate FROM Opportunity WHERE Oppo_OpportunityId = #Oppo_OpportunityId#)
SET @Comm_ToDateTime = (SELECT Oppo_biddate FROM Opportunity WHERE Oppo_OpportunityId = #Oppo_OpportunityId#)
SET @Comm_Note = '#Oppo_Description#'
SET @Comm_CreatedBy = '66'
SET @Comm_CreatedDate = GETDATE()
SET @Comm_TimeStamp = GETDATE()
SET @Comm_SecTerr = #Oppo_SecTerr#
SET @Comm_Subject = 'Prebid Project Biddate'
SET @COMM_UpdatedBy = '66'
SET @Comm_UpdatedDate = GETDATE()
SET @CMLI_CreatedBy = '66'
SET @CMLI_CreatedDate = GETDATE()
SET @CMLI_TimeStamp = GETDATE()
SET @CMLI_CommPersonId = (SELECT Oppo_PrimaryPersonId FROM Opportunity WHERE Oppo_OpportunityId = #Oppo_OpportunityId#)
SET @CMLI_CommCompanyId = (SELECT Oppo_PrimaryCompanyId FROM Opportunity WHERE Oppo_OpportunityId = #Oppo_OpportunityId#)
SET @CMLI_SalesPerson = #oppo_salesperson#

EXEC @Comm_CommId = eware_get_identity_id Communication

SET @CMLI_CommId = @Comm_CommId

INSERT INTO Communication (Comm_CommunicationId, Comm_OpportunityId, Comm_ChannelId, Comm_Type, Comm_Action, Comm_Status, Comm_Priority, Comm_DateTime, Comm_ToDateTime, Comm_Note, Comm_CreatedBy, Comm_CreatedDate, Comm_TimeStamp, Comm_SecTerr, Comm_Subject, Comm_UpdatedBy, Comm_UpdatedDate) VALUES (@Comm_CommId, @Comm_OppoId, '7', 'Appointment', 'biddate', 'Pending', 'Normal', @Comm_DateTime, @Comm_ToDateTime, @Comm_Note, @Comm_CreatedBy, @Comm_CreatedDate, @Comm_TimeStamp, @Comm_SecTerr, @Comm_Subject, @Comm_UpdatedBy, @Comm_UpdatedDate)

EXEC @CMLI_CommLinkId = eware_get_identity_id Comm_Link

SET @CMLI_UserId = '33'
INSERT INTO Comm_Link (CmLi_CommLinkId, CmLi_Comm_UserId, CmLi_Comm_CommunicationId, CmLi_CreatedBy, CmLi_CreatedDate, CmLi_TimeStamp, CmLi_Comm_PersonId, CmLi_Comm_CompanyId) VALUES (@CMLI_CommLinkId, @CMLI_UserId, @CMLI_CommId, @CMLI_CreatedBy, @CMLI_CreatedDate, @CMLI_TimeStamp, @CMLI_CommPersonId, @CMLI_CommCompanyId)

SET @CMLI_UserId = @CMLI_SalesPerson
EXEC @CMLI_CommLinkId = eware_get_identity_id Comm_Link
INSERT INTO Comm_Link (CmLi_CommLinkId, CmLi_Comm_UserId, CmLi_Comm_CommunicationId, CmLi_CreatedBy, CmLi_CreatedDate, CmLi_TimeStamp, CmLi_Comm_PersonId, CmLi_Comm_CompanyId) VALUES (@CMLI_CommLinkId, @CMLI_UserId, @CMLI_CommId, @CMLI_CreatedBy, @CMLI_CreatedDate, @CMLI_TimeStamp, @CMLI_CommPersonId, @CMLI_CommCompanyId)