sql server 存储过程

定义存储过程的语法

   CREATE PROC[EDURE] 存储过程名

             @参数1 数据类型 = 默认值,

              …… ,

             @参数n 数据类型 = 默认值 OUTPUT

           AS

           SQL语句

   GO

q  和C语言的函数一样，参数可选

q  参数分为输入参数、输出参数

q  输入参数允许有默认值

 

①  无参数的存储过程：（作用一般用来查询某些表的数据的信息，此时where或者having所跟的条件没有关于变量的，即为如果没有用存储过程的查询语句同样可以达到相同的目的，一般没必要用存储过程了）：

CREATE  PROC  stucourse

AS

SELECT sname,cname,grade

FROM  student s,coursec,sc

WHERE s.sno=sc.sno andc.cno=sc.cno

ORDER BYs.sno

GO

相当于：SELECT sname,cname,grade

FROM  student s,coursec,sc

WHERE s.sno=sc.sno andc.cno=sc.cno

ORDER BYs.sno

②  有输入参数的存储过程：（输入参数跟C语言等的输入参数是一个意思。这时肯定要在where或者having等条件语句后面跟上变量，作为查询语句的条件。）

CREATE  PROCEDURE  stusc

@snamevarchar(20)

AS

SELECT sname,cname,grade

FROM  student s,coursec,sc

WHERE s.sno=sc.sno andc.cno=sc.cno

and sname=@sname

GO

 

这样执行存储过程的语句时候就要添加参数：

Exec stusc  ‘李勇’

③  创建带输出参数的存储过程：（定义输出参数时候要加output以示区别，实际上输入参数也可以加input修饰，输出参数就是要把查到的信息在执行存储过程的时候输出，所以要输出参数就会紧接在select后面。这样就会把查出的信息直接赋值给了变量 ）

CREATE  PROCEDURE  stuscout   

@sname varchar(20) ,@sgrade int output --定义输出参数时候要加output以示区别

AS

SELECT @sgrade = sum(grade)   --输出参数就会紧接在select后面

FROM student,sc

WHEREstudent.sno=sc.sno

andsname=@sname

GO

 

        执行存储过程：

        Declare @sumgrade int

        Exec stuscout  ‘李勇’, @sumgradeoutput

        Select @sumgrade

go

 

创建有返回值的存储过程：

（1）简单的返回值，只有已知的0或者1

CREATE PROCEDURE find  @auName varchar(20)

AS

IF EXISTS(SELECT * FROM tb_authorinfo WHEREauthname = @auName)

  RETURN 1

ELSE     RETURN 0

GO

 

 

DECLARE @Resultint

EXECUTE @Result = find '王强'

IF (@Result=1)

  PRINT '找到此人'

ELSE

PRINT '查无此人'

GO

 

（2）以上的返回值是简单的0和1，可以不用在存储过程中定义返回值作为变量，然而下面这个就要：

 

建立一个名为ScroeProc的存储过程，它带有一个输入参数和两个输出参数和一个返回值。

输入参数：@Cname，接收课程名。

输出参数：@Avg接受平均分；@Max接受最高分。

返回值：局部变量@ErrorSave 存放错误号。

 

（系统函数@@ERROR：如果上一个Transact-SQL语句执行成功，@@ERROR 系统函数将返回 0；如果该语句生成错误，@@ERROR 将返回错误号）

 

Create proc ScroeProc @Cnamevarchar（20），@Avg decimal output，@Max decimal output

As

Declare @ErrorSave int

Set@ErrorSave=0

Select  @Avg=avg（成绩）@Max（成绩）from 成绩表 sc，课程表co

where  sc.sno=co.sno groupby 课程

having 课程=@cname --不一定要where条件后才能加变量的

If（@@ERROR<>0）

SET @ErrorSave=@@ERROR

SELECT @Max=Max(成绩)

  FROM成绩表 AS sc  INNER JOIN课程表 AS co

  ONsc.课程号=co.课程号

  GROUP BY课程

  HAVING课程=@Cname

IF (@@ERROR<>0)

   SET@ErrorSave=@@ERROR

RETURN @ErrorSave

GO  

 

 

调用存储过程：

 

DECLARE  @RetCode  int  ,

@AvgGrade decimal ,@MaxGradedecimal

 

EXEC @RetCode =ScroeProc  '计算机应用基础', @AvgGrade OUTPUT,@MaxGradeOUTPUT

 

select @RetCode,@AvgGrade,@MaxGrade

GO