1 SET ANSI_NULLS ON
2 GO
3 SET QUOTED_IDENTIFIER ON
4 GO
5 -- =============================================
6 -- Author: <Author,,Name>
7 -- Create date: <Create Date,,>
8 -- Description: <Description,,>
9 -- =============================================
10 alter PROCEDURE CourseProcedure
11 @C# int,
12 @Cname nvarchar(50),
13 @MESSAGE nvarchar(100) output----提示信息(输出参数)
14 AS
15 BEGIN
16 declare @Cnames nvarchar(20),@errmsg nvarchar(100),@sql nvarchar(200)
17
18 --动态执行sql语句 输出输入参数
19 set @sql='select @Cnames=Cname from Course where C#=@C#'
20 exec sp_executesql @sql,N' @Cnames nvarchar(20) out,@C# int',@Cnames out,@C#
21
22 --exec 执行没有带参数的动态sql
23 --直接执行sql语句
24 --exec ('select * from mytable')
25 --拼接sql语句
26 --SET @sql = N'SELECT COUNT(*) FROM ' + QUOTENAME(@schemaname) + N'.' + QUOTENAME(@tablename) + N';'
27 --exec(@sql)
28 --exec @sql
29
30 select * into #Course from Course
31 IF @@ROWCOUNT>0
32 begin
33 SELECT * FROM #Course
34 SELECT @MESSAGE =N'测试信息!'
35 RETURN
36 end
37
38 begin transaction --开始事务
39 begin try
40 select * from Course
41 commit transaction --提交事务
42 select @MESSAGE='成功'
43 end try
44 begin catch
45 rollback transaction --回滚事务
46 --返回错误信息
47 if (ERROR_SEVERITY()<>11)
48 begin
49 SELECT @errmsg='错误信息：' + Error_Message() + ' 过程：' + isnull(Error_Procedure(),'未知') + '。 行号：' + convert(varchar
50 ,Error_Line()) + '。';
51 raiserror(@errmsg, 16, 1);
52 end
53 else
54 begin
55 SELECT @errmsg=Error_Message();
56 raiserror(@errmsg, 11, 1);
57 end
58 end catch
59 END
60 GO
61
62 --存储过程调用
63 DECLARE @C# int, @Cname nvarchar(80), @MESSAGE nvarchar(80)
64 set @C#=1
65 set @Cname='11'
66 exec CourseProcedure @C#,@Cname,@MESSAGE output
67 select @MESSAGE
68 exec CourseProcedure 1,'11',''
