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 CourseProcedure11     @C# int,12     @Cname nvarchar(50),13     @MESSAGE nvarchar(100) output----提示信息(输出参数)14 AS15 BEGIN16     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 执行没有带参数的动态sql23     --直接执行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 @sql29     30     select * into #Course from Course31     IF @@ROWCOUNT>032     begin33        SELECT * FROM #Course34        SELECT @MESSAGE  =N‘测试信息!‘35        RETURN36     end37     38     begin transaction --开始事务39     begin try40       select * from Course41       commit transaction --提交事务42       select @MESSAGE=‘成功‘43     end try44     begin catch45       rollback transaction --回滚事务46       --返回错误信息47       if (ERROR_SEVERITY()<>11)48       begin49          SELECT @errmsg=‘错误信息：‘ + Error_Message() + ‘ 过程：‘ + isnull(Error_Procedure(),‘未知‘) + ‘。 行号：‘ + convert(varchar50          ,Error_Line()) + ‘。‘;51          raiserror(@errmsg, 16, 1);52       end53       else54       begin55          SELECT @errmsg=Error_Message();56          raiserror(@errmsg, 11, 1);57       end58     end catch59 END60 GO61 62 --存储过程调用63 DECLARE @C# int, @Cname nvarchar(80), @MESSAGE nvarchar(80)64 set @C#=165 set @Cname=‘11‘66 exec CourseProcedure @C#,@Cname,@MESSAGE output 67 select @MESSAGE68 exec CourseProcedure 1,‘11‘,‘‘