CREATE PROC USP_GroupTable
@idtable1 INT, @idtable2 INT
AS
BEGIN
	DECLARE @idfirstBill INT
	DECLARE @idsecondBill INT

	DECLARE @isfirstTableEmpty INT =1
	DECLARE @issecondTableEmpty INT =1

	SELECT @idfirstBill=id FROM dbo.Bill WHERE idTable = @idtable1 AND statusBill = 0
	SELECT @idsecondBill=id FROM dbo.Bill WHERE idTable = @idtable2 AND statusBill = 0

	IF(@idfirstBill IS NULL)
	BEGIN
		INSERT dbo.Bill
		        ( DateCheckin ,
		          DateCheckout ,
		          idTable ,
		          statusBill ,
		          Discout ,
		          TotalPrice
		        )
		VALUES  ( GETDATE() , -- DateCheckin - date
		          NULL , -- DateCheckout - date
		          @idtable1, -- idTable - int
		          0 , -- statusBill - int
		          0 , -- Discout - int
		          0.0  -- TotalPrice - float
		        )
		SELECT @idfirstBill=MAX(id) FROM dbo.Bill WHERE idTable =@idtable1 AND statusBill = 0
	END
	 SELECT @isfirstTableEmpty = COUNT(*) FROM dbo.Billinfo WHERE idBill = @idfirstBill
	IF(@idsecondBill IS NULL)
	BEGIN
		INSERT dbo.Bill
		        ( DateCheckin ,
		          DateCheckout ,
		          idTable ,
		          statusBill ,
		          Discout ,
		          TotalPrice
		        )
		VALUES  ( GETDATE() , -- DateCheckin - date
		          NULL , -- DateCheckout - date
		          @idtable2 , -- idTable - int
		          0 , -- statusBill - int
		          0 , -- Discout - int
		          0.0  -- TotalPrice - float
		        )
		SELECT @idsecondBill=MAX(id) FROM dbo.Bill WHERE idTable =@idtable2 AND statusBill = 0
    END
	SELECT @issecondTableEmpty =COUNT(*) FROM dbo.Billinfo WHERE idBill = @idsecondBill
    
    UPDATE dbo.Billinfo SET idBill=@idfirstBill WHERE idBill = @idfirstBill
	UPDATE dbo.Billinfo SET idBill =@idfirstBill WHERE idBill = @idsecondBill

	IF(@isfirstTableEmpty = 0)
		UPDATE dbo.TableFood SET statusfood = N'Trống' WHERE id = @idtable2
	IF(@issecondTableEmpty = 0)
		UPDATE dbo.TableFood SET statusfood = N'Trống' WHERE id = @idtable1
END	
GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63