5WbW7XV4

1-- BEGIN TRANSACTION (HOW TO DO?)
2
3UPDATE Bookings
4 SET TicketsBooked = TicketsBooked + @TicketsToBook
5 WHERE FlightId = @Id AND TicketsMax < (TicketsBooked + @TicketsToBook)
6
7-- Here I need to insert only if the row doesn't exists.
8-- If the row exists but the condition TicketsMax is violated, I must not insert the row and return FALSE
9
10IF @@ROWCOUNT = 0 
11BEGIN
12
13 INSERT INTO Bookings ... (omitted)
14
15END
16
17-- END TRANSACTION (HOW TO DO?)
18
19-- Return TRUE (How to do?)
20	
21if exists(select 1 from INFORMATION_SCHEMA.TABLES T where T.TABLE_NAME = 'Bookings') begin
22	drop table Bookings
23end
24GO
25
26create table Bookings(
27	FlightID		int identity(1, 1) primary key,
28	TicketsMax		int not null,
29	TicketsBooked	int not null
30)
31GO
32
33insert	Bookings(TicketsMax, TicketsBooked) select 1, 0
34insert	Bookings(TicketsMax, TicketsBooked) select 2, 2
35insert	Bookings(TicketsMax, TicketsBooked) select 3, 1
36GO
37
38select * from Bookings
39
40declare	@FlightID int = 1
41declare	@TicketsToBook int = 2
42
43--; This should add a new record
44merge	Bookings as T
45using	(select	@FlightID as FlightID, @TicketsToBook as TicketsToBook) as S
46		on	T.FlightID = S.FlightID
47			and T.TicketsMax > (T.TicketsBooked + S.TicketsToBook)
48	when matched then
49		update set T.TicketsBooked = T.TicketsBooked + S.TicketsToBook
50	when not matched then
51		insert (TicketsMax, TicketsBooked) 
52		values(S.TicketsToBook, S.TicketsToBook);
53
54select * from Bookings
55	
56IF EXISTS (SELECT * FROM Bookings WHERE FLightID = @Id)
57BEGIN
58    --UPDATE HERE
59END
60ELSE
61BEGIN
62   -- INSERT HERE
63END
64	
65begin tran /* default read committed isolation level is fine */
66
67if not exists (select * from Table with (updlock, rowlock, holdlock) where ...)
68    /* insert */
69else
70    /* update */
71
72commit /* locks are released here */
73	
74declare @rowCount int
75
76select @rowCount=@@RowCount
77
78if @rowCount=0
79begin
80--insert....
81	
82set ANSI_NULLS ON
83set QUOTED_IDENTIFIER ON
84GO
85ALTER PROCEDURE [dbo].[cjso_UpdateCustomerLogin]
86    (
87      @CustomerID AS INT,
88      @UserName AS VARCHAR(25),
89      @Password AS BINARY(16)
90    )
91AS 
92    BEGIN
93    	IF ISNULL((SELECT CustomerID FROM tblOnline_CustomerAccount WHERE CustomerID = @CustomerID), 0) = 0
94    	BEGIN
95    		INSERT INTO [tblOnline_CustomerAccount] (
96    			[CustomerID],
97    			[UserName],
98    			[Password],
99    			[LastLogin]
100    		) VALUES ( 
101    			/* CustomerID - int */ @CustomerID,
102    			/* UserName - varchar(25) */ @UserName,
103    			/* Password - binary(16) */ @Password,
104    			/* LastLogin - datetime */ NULL ) 
105    	END
106    	ELSE
107    	BEGIN
108    		UPDATE  [tblOnline_CustomerAccount]
109    		SET     UserName = @UserName,
110    				Password = @Password
111    		WHERE   CustomerID = @CustomerID	
112    	END
113
114    END