1 -----存储过程的学习
2 --不带参数没有返回值的存储过程
3 /*
4 create proc usp_存储过程的名字
5 as
6 begin
7 --语句
8 end
9 */
10
11 create proc usp_Getdt_article
12 as
13 begin
14 select * from dbo.dt_article
15 end
16 go
17 -----------------------------------------------------------
18 -----------------------------------------------------------
19
20 --带参数的存储过程
21 /*
22 create proc usp_带参数存储过程名字
23 @参数名 类型,
24 @参数名 类型
25 as
26 begin
27 --语句
28 end
29 */
30 -------------------------------------------------------------
31 -------------------------------------------------------------
32 -- 使用存储过程实现转账
33 create proc usp_TransMoney
34 @from char(4),
35 @to char(4),
36 @money money
37 as
38 begin
39 begin transaction
40 begin try
41 update bank set balance = balance - @money where cId = @from;
42 update bank set balance = balance + @money where cId = @to;
43 commit transaction;
44 end try
45 begin catch
46 rollback transaction;
47 end catch;
48 end
49
50 ----------------------------------------------------------------
51 ----------------------------------------------------------------
52 -- 带有默认参数存储过程的语法
53 /*
54 create proc usp_带参数的存储过程
55 @参数名 类型 = 默认值,
56 @参数名 类型
57 as
58 begin
59 -- 语句
60 end
61 */
62
63 create proc usp_GetStudent
64 @stuNameTemp nvarchar(20) = 'all'
65 as
66 begin
67 if(@stuNameTemp = 'all')
68 begin
69 --语句
70 end
71 else
72 begin
73 --语句
74 end
75 end
76 go
