存储过程

--例1USE SUGOCreate Procedure SelProcASSelect * From SCGO--例2USE SUGOCreate Procedure CountProcASSelect Count(*) AS 总数 From CourseGo

USE SUGOExec SelProcExec CountProc

--例1 创建 带输入参数 的存储过程USE SUGOCreate Procedure QueryById @Sno varchar(10)ASSelect *from SCWhere Sno = @SnoGO--执行GOExecute QueryById ‘1513032087‘Execute QueryById @Sno = ‘1513032087‘--创建 带默认参数 的存储过程USE SUGOcreate Procedure QueryById2 @Sno varchar(10) = ‘1513032087‘ASSelect *From SCwhere Sno = @SnoGO--执行(可以不带参数, 因为有默认参数)EXecute QueryById2

--例4Use SUGOCreate Procedure QueryById3 @Sno varchar(10) = ‘1513032087‘,@Cnocount INT OUTPUT   --输出参数,Sno学号选的课程数ASSelect @Cnocount = Count(SC.Cno)From SC, Studentwhere SC.Sno = Student.Sno and SC.Sno = @SnoGO--执行--必须先声明！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！Declare @Sno varchar(10) = ‘1513032087‘;Declare @Cnocount int;                     --声明之后再执行EXEC QueryById3 @Sno, @Cnocount OUTPUTSelect ‘学号为‘ + @Sno + ‘选了‘ + Ltrim(STR(@Cnocount)) + ‘门课‘  --字符串还需要处理一下GO

------------管理存储过程--------------------修改存储过程exec CountProc   --查询GOAlter Procedure CountProcASselect Sno, COUNT(*) AS 总数From SCGroup By Sno--查看属性GOselect OBJECT_DEFINITION(OBJECT_ID(‘QueryById‘));  --select OBJECT_DEFINITION(OBJECT_ID(‘存储过程名‘));Exec sp_help QueryById      Exec sp_helptext QueryById--删除操作GODROP procedure 存储过程