Multiple inserts and selects in one stored procedure
I want to create a procedure that will
A) check to see if TicketNumberInput is in table Tix_number.TicketNumber.
B) if it does exisist, update the record to set UID to @uid and Set the Claimdate to GetDate()
AND ...
C) INSERT A RECORD INTO ANOTHER TABLE (TRANSACTION LOG TABLE)
If the record does exist, simply add a record to the transaction log with STATUS = 2 (failed).
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 Beg开发者_Go百科in End didn't seem to work
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!!!
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
精彩评论