· 7 years ago · Dec 04, 2018, 04:18 PM
1CREATE TABLE Region
2(
3 RegionID INT IDENTITY(1,1)
4 ,RegionName VARCHAR(25)
5)
6
7GO
8
9INSERT INTO Region(RegionName)
10VALUES ('East Coast')
11 ,('Mid West')
12 ,('West Coast')
13
14GO
15
16CREATE TABLE Customer
17(
18 CustomerID INT IDENTITY(1,1)
19 ,FirstName VARCHAR(5)
20 ,Region INT
21)
22
23GO
24
25INSERT INTO Customer(FirstName,Region)
26VALUES('Tom',1)
27 ,('Mike',2)
28 ,('Jean',3)
29
30GO
31
32CREATE TABLE Orders
33(
34 OrderID INT IDENTITY(1,1)
35 ,CustomerID INT
36 ,OrderAmount INT
37 ,OrderDate DATE
38)
39
40GO
41
42INSERT INTO Orders(CustomerID,OrderAmount,OrderDate)
43VALUES(1,10,'2018-11-30')
44 ,(2,12,'2018-11-30')
45 ,(2,15,'2018-12-01')
46 ,(2,8,'2018-12-02')
47 ,(2,11,'2018-12-03')
48 ,(3,13,'2018-12-01')
49 ,(3,20,'2018-12-03')
50
51GO
52
53CREATE PROCEDURE udsp_GetOrdersOfXAmount @OrderAmount INT, @RegionID INT = 0
54AS
55BEGIN
56
57DECLARE @ProcedureTemp TABLE
58(
59 OrderID INT
60 ,CustomerID INT
61 ,OrderAmount INT
62 ,OrderDate DATE
63)
64
65INSERT INTO @ProcedureTemp(OrderID,CustomerID,OrderAmount,OrderDate)
66SELECT * FROM Orders WHERE OrderAmount >= @OrderAmount
67
68--Do several other UPDATES/ DELETES to @ProcedureTemp
69
70--This is where the issue lies
71IF @RegionID > 0
72BEGIN
73 DELETE T FROM @ProcedureTemp T
74 WHERE NOT EXISTS
75 (
76 SELECT *
77 FROM Customer C
78 JOIN @ProcedureTemp T ON T.CustomerID = C.CustomerID
79 WHERE C.Region = @RegionID
80 )
81END
82
83SELECT * FROM @ProcedureTemp
84
85END
86
87GO
88
89EXEC udsp_GetOrdersOfXAmount 10,3
90
91DECLARE @OrderAmount INT = 10, @RegionID INT = 3
92
93DECLARE @ProcedureTemp TABLE
94(
95 OrderID INT
96 ,CustomerID INT
97 ,OrderAmount INT
98 ,OrderDate DATE
99)
100
101INSERT INTO @ProcedureTemp(OrderID,CustomerID,OrderAmount,OrderDate)
102SELECT * FROM Orders WHERE OrderAmount >= @OrderAmount
103
104SELECT *
105FROM Customer C
106 JOIN @ProcedureTemp T ON T.CustomerID = C.CustomerID
107WHERE C.Region = @RegionID