sql insert stored procedure with if

Question

Insert into xHH_QuickScan
(
    ScanID,
    EmployeeID,
    ProjectID,
    Barcode,
    Scandate,
    Scantime,
    ManualEntry,
    Category,
    Date1,
    Date2,
    Serial,
    Descrid,
    Date3,
    AssetID,
    Date4,
    AStatus,
    BarcodeNew,
    SerialNew,
    DescrNew
)
Values
(
/****** check DescID value ******/
if @AssetID = ''
    Begin
        Set @Descrid = ''
    End
Else
    Begin
         Set @Descrid = @Descrid
        END
End

    @ScanID,
    @EmployeeID,
    @ProjectID,
    @Barcode,
    @Scandate,
    @Scantime,
    @ManualEntry,
    @Category,
    @Date1,
    @Date2,
    @Serial,
    @Descrid,
    @Date3,
    @AssetID,
    @Date4,
    @AStatus,
    @BarcodeNew,
    @SerialNew,
    @DescrNew
)

This throws an error. I've also tried CASE, with errors. I need to test the value of @AssetID and alter the value of @Descrid accordingly

Answer 1

Yes, it will. You can't use procedural code in a values statement, because they're not values.

Try

insert xHHQuickScan (ScanID, DescID, ... )
select 
      @ScanID,
      case Isnull(@assetID,'') = '' then ''
                  else @DescID 
      end, 
      ...

Answer 2

Try this:

Insert into xHH_QuickScan
    (
    ScanID,
    EmployeeID,
    ProjectID,
    Barcode,
    Scandate,
    Scantime,
    ManualEntry,
    Category,
    Date1,
    Date2,
    Serial,
    Descrid,
    Date3,
    AssetID,
    Date4,
    AStatus,
    BarcodeNew,
    SerialNew,
    DescrNew
    )
Values
    (
    @ScanID,
    @EmployeeID,
    @ProjectID,
    @Barcode,
    @Scandate,
    @Scantime,
    @ManualEntry,
    @Category,
    @Date1,
    @Date2,
    @Serial,
    (CASE WHEN @AssetID = '' THEN '' ELSE @Descrid END),
    @Date3,
    @AssetID,
    @Date4,
    @AStatus,
    @BarcodeNew,
    @SerialNew,
    @DescrNew
    )

Answer 3

Personally, I prefer this manner.

/****** check DescID value ******/
if @AssetID = ''
    Begin
        Set @Descrid = ''
    End

Insert into xHH_QuickScan
(
    ScanID,
    EmployeeID,
    ProjectID,
    Barcode,
    Scandate,
    Scantime,
    ManualEntry,
    Category,
    Date1,
    Date2,
    Serial,
    Descrid,
    Date3,
    AssetID,
    Date4,
    AStatus,
    BarcodeNew,
    SerialNew,
    DescrNew
)
Values
(
    @ScanID,
    @EmployeeID,
    @ProjectID,
    @Barcode,
    @Scandate,
    @Scantime,
    @ManualEntry,
    @Category,
    @Date1,
    @Date2,
    @Serial,
    @Descrid,
    @Date3,
    @AssetID,
    @Date4,
    @AStatus,
    @BarcodeNew,
    @SerialNew,
    @DescrNew
    )