HelixTitan HelixTitan - 1 month ago 7
SQL Question

Case in an INSERT INTO Statement

I am trying to set the

ActionReq
column of this stored procedure to the value of Expiration + the AdvancedCancel when an ActionReq is not supplied.
ActionReq
and Expiration are datetimes and AdvancedCancel is an int. How do I do thi? My SQL skills are very basic.

ALTER PROCEDURE [dbo].[insertIntoGrid]
@Vendor nvarchar(max),
@Product nvarchar(max),
@Type nvarchar(max),
@AccountCode nvarchar(max),
@2016AccCode nvarchar(max),
@BusinessUnit nvarchar(max),
@Group nvarchar(max),
@TIAYTD nvarchar(max),
@ContractPeriod nvarchar(max),
@RenewPeriod nvarchar(max),
@Expiration datetime,
@AdvancedCancel nvarchar(max),
@ActionReq datetime,
@Notes nvarchar(max),
@Division nvarchar(max)
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO TestRedo3(Vendor, [Product/Service], [Type], AccountCode,
[2016AccCode], BusinessUnit, [Group], TIAYTD,
ContractPeriod, RenewPeriod, Expiration, AdvancedCancel,
case [ActionReq] is null THEN dateadd(day, @AdvancedCancel, @Expiration)
else ActionReq
end as ActionReq, Notes, Division)
VALUES (@Vendor, @Product, @Type, @AccountCode,
@2016AccCode, @BusinessUnit, @Group, @TIAYTD,
@ContractPeriod, @RenewPeriod, @Expiration, @AdvancedCancel,
@ActionReq, @Notes, @Division)
END

Answer

You would need to change your insert to something like this. I am not totally sure what you are trying to do but this should be close.

INSERT INTO TestRedo3
(
    Vendor,
    [Product/Service],
    [Type],
    AccountCode,
    [2016AccCode],
    BusinessUnit,
    [Group],
    TIAYTD,
    ContractPeriod,
    RenewPeriod,
    Expiration,
    AdvancedCancel,
    ActionReq,
    Notes,
    Division
) 
select 
    @Vendor,
    @Product,
    @Type,
    @AccountCode,
    @2016AccCode,
    @BusinessUnit,
    @Group,
    @TIAYTD,
    @ContractPeriod,
    @RenewPeriod,
    @Expiration,
    @AdvancedCancel,
    case when @ActionReq is null THEN dateadd(day, @AdvancedCancel, @Expiration) 
        else @ActionReq
    end,
    @Notes,
    @Division
Comments