如何将选择案例语句与 SQL 事务一起使用

Question

我有一个CRM应用程序。 我在数据库实现中找到查询，而工作人员用户发布查询回复，我必须将新的查询和查询插入到一个表中并同时修改另一个表数据。 我应用了逻辑以及我代表我存储的过程，但在这个过程中发生了错误。

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InquiryPostReply]
(
    @Inquiry_id VARCHAR(50),
    @Priority_type VARCHAR(25),
    @Status_name VARCHAR(50),
    @Inquiry_Content VARCHAR(1024),
    @NewId VARCHAR(50) OUT
)
AS
BEGIN
SET NOCOUNT ON;
declare @var1 int
declare @var2 int
declare @uniqueRef char(14)
set @uniqueRef = dbo.UniqueRefNum(rand(), rand(), rand(), rand())
set @var1= (SELECT [Id] FROM [OmStocks].[dbo].[tbl_Status_master] WHERE (Status_name=@Status_name))
set @var2= (SELECT [Id] FROM [OmStocks].[dbo].[tbl_Priority_master] WHERE (Priority_name=@Priority_type))
    SELECT 
        CASE @Status_name
        WHEN 'Open' THEN
            BEGIN TRAN;
            BEGIN TRY
                INSERT INTO [OmStocks].[dbo].[tbl_Inquiry_master]
                ([Id],[Inquiry_id],[Priority_id],[Status_id],[Inquiry_Content],[TimeStamp])
                VALUES
                (@uniqueRef,@Inquiry_id,@var2,@var1,@Inquiry_Content,CONVERT(DATETIME,GETDATE(), 101))
                UPDATE [OmStocks].[dbo].[tbl_Inquiry_History] 
                    SET [Priority_id] = @var2,[Status_id] = @var1,[IsDisplay] = 1,[IsReplied] = 1,[TimeStamp] = CONVERT(DATETIME,GETDATE(), 101)
                WHERE (Inquiry_id=@Inquiry_id)
            COMMIT TRAN;
            END TRY;
        WHEN 'Close' THEN
            BEGIN TRAN;
            BEGIN TRY
                INSERT INTO [OmStocks].[dbo].[tbl_Inquiry_master]
                ([Id],[Inquiry_id],[Priority_id],[Status_id],[Inquiry_Content],[TimeStamp])
                VALUES
                (@uniqueRef,@Inquiry_id,@var2,@var1,@Inquiry_Content,CONVERT(DATETIME,GETDATE(), 101))
                UPDATE [OmStocks].[dbo].[tbl_Inquiry_History] 
                    SET [Priority_id] = @var2,[Status_id] = @var1,[IsDisplay] = 0,[IsReplied] = 1,[TimeStamp] = CONVERT(DATETIME,GETDATE(), 101),[Activity_expire_time] = CONVERT(DATETIME,GETDATE(), 101)
                WHERE (Inquiry_id=@Inquiry_id)
            COMMIT TRAN;
            END TRY; 
    END
    SET @NewId = @uniqueRef
END

发生错误如下：

Msg 156, Level 15, State 1, Procedure InquiryPostReply, Line 21
Incorrect syntax near the keyword 'BEGIN'.
Msg 102, Level 15, State 1, Procedure InquiryPostReply, Line 31
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Procedure InquiryPostReply, Line 43
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Procedure InquiryPostReply, Line 46
Incorrect syntax near 'END'.

请帮帮我...

Answer 1

您不能为此使用 CASE。 CASE 是返回单个结果的表达式，而不是可用于流控制的语句。我知道CASE在其他一些语言中是这样使用的，但在 T-SQL 中是不可能的。

IF @Status_name = 'Open' THEN
BEGIN
  -- do stuff
END
IF @Status_name = 'Close' THEN
BEGIN
  -- do other stuff
END