Qa9EDzNW

1CREATE PROC [dbo].[sp_helloworld]
2AS
3BEGIN
4    SELECT 'Hello World'
5    DECLARE @sSQL VARCHAR(1000)
6    SET @sSQL = 'CREATE PROC [dbo].[sp_helloworld2]
7            AS
8            BEGIN
9                SELECT ''Hello World 2''
10            END'
11    EXEC (@sSQL)
12
13    EXEC [sp_helloworld2];
14    DROP PROC [sp_helloworld2];
15END 
16     
17The module 'sp_helloworld' depends on the missing object 'sp_helloworld2'.
18The module will still be created; however, it cannot run successfully until
19the object exists. 
20     
21Hello World
22Hello World 2 
23     
24CREATE TABLE #t1 (digit INT, name NVARCHAR(10));  
25GO
26
27CREATE PROCEDURE #insert_to_t1  
28(  
29    @digit INT  
30,    @name NVARCHAR(10)  
31)  
32AS  
33BEGIN  
34    merge #t1 AS tgt  
35    using (SELECT @digit, @name) AS src (digit,name)  
36    ON    (tgt.digit = src.digit)  
37    WHEN matched THEN  
38          UPDATE SET name = src.name  
39    WHEN NOT matched THEN  
40          INSERT (digit,name) VALUES (src.digit,src.name);  
41END;  
42GO  
43
44
45EXEC #insert_to_t1 1,'One';  
46EXEC #insert_to_t1 2,'Two';  
47EXEC #insert_to_t1 3,'Three';  
48EXEC #insert_to_t1 4,'Not Four';  
49EXEC #insert_to_t1 4,'Four'; --update previous record!  
50
51
52SELECT    * FROM #t1; 
53     
54select name, userID, fnCaseCount(userID), fnRefCount(UserID)
55  from table1 t1
56  left join table2 t2
57    on t1.userID = t2.UserID 
58     
59select t1.UserID, t2.name, v1.CaseCount, v2.RefCount
60  from table1 t1
61  left join table2 t2
62    on t1.userID = t2.UserID
63  left join vwCaseCount v1
64    on v1.UserID = t1.UserID
65  left join vwRefCount v2
66    on v2.UserID = t1.UserID 
67     
68insert into body ...
69
70goto BODY1_RET 
71     
72INSERT INTO body....
73
74goto BODY2_RET 
75     
76insert into header
77
78if @fork=1 goto HEADER_RET1
79
80if @fork=2 goto HEADER_RET2
81
82select 1/0 --flow check!