DgQ6ywhU

1USE Snoop
2GO
3
4/* need to prebuild the table for the sake of stability */
5CREATE TABLE Snoop.Crib (Ma VARCHAR(255));
6GO
7
8INSERT INTO Snoop.Crib(Ma)
9VALUES ('pimp');
10GO
11
12CREATE TABLE Snoop.PushPop (it INT);
13GO
14
15CREATE OR
16ALTER PROCEDURE Snoop.RebuildCrib AS
17BEGIN
18    IF NOT EXISTS(SELECT NULL
19                    FROM INFORMATION_SCHEMA.TABLES
20                   WHERE TABLE_SCHEMA = 'Snoop'
21                     AND TABLE_NAME = 'Crib')
22        BEGIN
23            CREATE TABLE Snoop.Crib (Ma VARCHAR(255));
24            INSERT INTO Snoop.Crib(Ma)
25            VALUES ('pimp');
26        END;
27    SELECT 'Pimps are in the crib'
28END;
29GO
30
31CREATE OR
32ALTER PROCEDURE Snoop.DoItLikeItsHot(doWhat VARCHAR) AS
33BEGIN
34    DECLARE @hotLoopCount INT =0
35    DECLARE @hotLoopMax INT =3
36
37    IF doWhat = 'Drop'
38        BEGIN
39            SELECT 'Dropping it'
40            WHILE @hotLoopCount < @hotLoopMax
41                BEGIN
42                    DROP TABLE IF EXISTS Snoop.Crib
43                    SET @hotLoopCount = @hotLoopCount + 1
44                    EXEC Snoop.RebuildCrib
45                END;
46        END;
47
48    IF doWhat = 'Park'
49        BEGIN
50            SELECT 'Parking for 1 second'
51            WHILE @hotLoopCount < @hotLoopMax
52                BEGIN
53                    SET @hotLoopCount = @hotLoopCount + 1
54                    WAITFOR DELAY '00:00:01'
55                END;
56        END;
57
58    IF doWhat = 'Pop'
59        BEGIN
60            SELECT 'Identified attitude, pushing and popping'
61            WHILE @hotLoopMax - @hotLoopCount > 0
62                BEGIN
63                    INSERT INTO Snoop.PushPop @hotLoopCount
64                    COMMIT
65                    SET @hotLoopCount = @hotLoopCount + 1
66                END;
67            WHILE @hotLoopCount < @hotLoopMax
68                BEGIN
69                    DELETE FROM Snoop.PushPop WHERE it = @hotLoopCount
70                    COMMIT
71                    SET @hotLoopCount = @hotLoopCount + 1
72                END;
73
74        END;
75
76    SELECT REPLICATE(doWhat + ' it like its hot, ', @hotLoopCount)
77END;
78GO
79
80CREATE OR
81ALTER PROCEDURE Snoop.ChorusCribPimps AS
82BEGIN
83    DECLARE @pimpsInCribMa INT
84    EXEC Snoop.RebuildCrib
85    SELECT COUNT(*) INTO @pimpsInCribMa FROM Snoop.Crib
86    IF @pimpsInCribMa >= 1 EXEC Snoop.DoItLikeItsHot doWhat = 'Drop'
87END;
88GO
89
90CREATE OR
91ALTER PROCEDURE Snoop.ChorusPigsTry AS
92BEGIN
93    DECLARE @pigs VARCHAR = 'get'
94    DECLARE @you VARCHAR
95    SELECT CURRENT_USER INTO @you
96    BEGIN TRY
97        IF @pigs = @you
98            EXEC Snoop.DoItLikeItsHot doWhat = 'Park'
99    END TRY
100    BEGIN CATCH
101        SELECT NULL
102    END CATCH
103END;
104GO
105
106CREATE OR
107ALTER PROCEDURE Snoop.ChorusGetTude AS
108BEGIN
109    DECLARE @user VARCHAR = 'attitude'
110    IF @user = 'attitude'
111        EXEC Snoop.DoItLikeItsHot doWhat = 'Pop'
112END;
113GO
114
115CREATE OR
116ALTER PROCEDURE Snoop.ChorusLeadOut AS
117BEGIN
118    DECLARE @onTheArm VARCHAR= 'Rollie'
119    DECLARE @pourin VARCHAR = 'Chandon'
120
121    SELECT 'Arm has ' + @onTheArm AS OnMyArm,
122           'Pourin ' + @pourin    AS ImPourin
123
124    DECLARE @weedQuality VARCHAR = 'Best'
125
126    IF @weedQuality = 'Best'
127        ROLLBACK
128
129END;
130GO
131
132CREATE OR
133ALTER PROCEDURE Snoop.ChorusLikeItsHot AS
134BEGIN
135    EXEC Snoop.ChorusCribPimps
136    EXEC Snoop.ChorusPigsTry
137    EXEC Snoop.ChorusGetTude
138    EXEC Snoop.ChorusLeadOut
139
140END;