时间戳

首页 > 解决方案 > 如何在 CASE WHEN 语句中包含 INSERT

sql - 如何在 CASE WHEN 语句中包含 INSERT

问题描述

我想在语句完成INSERT时记录到表中。CASE WHEN否则INSERT将一些其他记录放入表中。

SELECT CASE
    SELECT CASE WHEN @AmountPaid > (SELECT InvAmount from Invoice where InvNo = @InvNo) THEN
    INSERT INTO PaymentMemo (fld1,fld2,fld3) values(valu1,valu2,valu3)
    WHEN @AmountPaid = (SELECT InvAmount from Invoice where InvNo = @InvNo) THEN
    INSERT INTO PaymentMemo (fld1,fld2,fld3) values(valu4,valu5,valu6)
END

如何使用INSERT具有两个条件的语句。

标签： sqlsql-server

解决方案

使用它：

insert into PaymentMemo (fld1,fld2,fld3)
SELECT CASE WHEN @AmountPaid > InvAmount then valu1 WHEN @AmountPaid = InvAmount then valu4 end,  
       CASE WHEN @AmountPaid > InvAmount then valu2 WHEN @AmountPaid = InvAmount then valu5 end,
       CASE WHEN @AmountPaid > InvAmount then valu3 WHEN @AmountPaid = InvAmount then valu6 end,
from Invoice where InvNo = @InvNo

或使用动态语句，如：

declare @InsertStmnt nvarchar(max)

SELECT @InsertStmnt=  CASE
    SELECT CASE WHEN @AmountPaid > (SELECT InvAmount from Invoice where InvNo = @InvNo) THEN
    'INSERT INTO PaymentMemo (fld1,fld2,fld3) values(valu1,valu2,valu3)'
    WHEN @AmountPaid = (SELECT InvAmount from Invoice where InvNo = @InvNo) THEN
    'INSERT INTO PaymentMemo (fld1,fld2,fld3) values(valu4,valu5,valu6)'
END

exec (@InsertStmnt)

你可以通过valu1,valu2,valu3或valu4,valu5,valu6动态

推荐阅读