在一个存储过程中进行多个插入和选择

I want to create a procedure that will

我想创建一个过程

A) check to see if TicketNumberInput is in table Tix_number.TicketNumber.

A)检查TicketNumberInput是否在table Tix_number.TicketNumber中。

B) if it does exisist, update the record to set UID to @uid and Set the Claimdate to GetDate()

B)如果它确实存在，则更新记录，将UID设置为@uid，并将Claimdate设置为GetDate()

AND ...

和…

C) INSERT A RECORD INTO ANOTHER TABLE (TRANSACTION LOG TABLE)

C)将记录插入另一个表(事务日志表)

If the record does exist, simply add a record to the transaction log with STATUS = 2 (failed).

如果该记录确实存在，只需向状态为2的事务日志添加一条记录(失败)。

This is my code so far, but it doesn't seem to have a problem checking the IF statements AND updating the record AND inserting another record. How do I add many checks into a statement. The Begin End didn't seem to work

到目前为止，这是我的代码，但是检查IF语句并更新记录并插入另一条记录似乎没有问题。如何在语句中添加许多检查。开头的结尾似乎没有效果

create procedure [dbo].[UpdateTicketNumber]
@TicketNumberInput uniqueidentifier, 
@UID   int
as 
IF EXISTS(
BEGIN
    SELECT *
    FROM [tixtowin].[dbo].[Tix_Number]
    where @TicketNumberInput = Tix_Number.TicketNumber)
    PRINT 'CORRECT RECORD'

    Update Tix_Number 
    Set Tix_Number.UID = @uid, ClaimDate = GETDATE()
    where TicketNumber = @TicketNumberInput

    /* Success - insert transaction message. Status = 1 */
    INSERT INTO [Tix_Transaction]
    ([Tix_Number],[UID], [status], [Datetime])
    VALUES
    (@TicketNumberInput, @UID, '1', GetDate())

    End
    ELSE

    /* Failed - insert transaction message. Status = 2 */
    INSERT INTO [Tix_Transaction]
    ([Tix_Number],[UID], [status], [Datetime])
    VALUES
    (@TicketNumberInput, @UID, '2', GetDate())

    PRINT 'INCORRECT RECORD'

THANK YOU for your help!!!

谢谢你的帮助!!!

1 个解决方案

#1

You've got no test as part of your IF. You need to test, then do something:

如果你没有测试的话。你需要测试，然后做一些事情:

create procedure [dbo].[UpdateTicketNumber] 
@TicketNumberInput uniqueidentifier,  
@UID   int 

as  
IF EXISTS (SELECT NULL
    FROM [tixtowin].[dbo].[Tix_Number] 
    where @TicketNumberInput = Tix_Number.TicketNumber) 
BEGIN 

    PRINT 'CORRECT RECORD' 

    Update Tix_Number  
    Set Tix_Number.UID = @uid, ClaimDate = GETDATE() 
    where TicketNumber = @TicketNumberInput 

    /* Success - insert transaction message. Status = 1 */ 
    INSERT INTO [Tix_Transaction] 
    ([Tix_Number],[UID], [status], [Datetime]) 
    VALUES 
    (@TicketNumberInput, @UID, '1', GetDate()) 

END 
ELSE 
BEGIN 
    /* Failed - insert transaction message. Status = 2 */ 
    INSERT INTO [Tix_Transaction] 
    ([Tix_Number],[UID], [status], [Datetime]) 
    VALUES 
    (@TicketNumberInput, @UID, '2', GetDate()) 

    PRINT 'INCORRECT RECORD' 
END