SQL stored procedure

(1) Without parameters:

Use studb

Go

If exists (select * From sysobjects where name = 'proc _ stu ')

Drop procedure proc_stu

Go

Create procedure proc_stu

As

Declare @ writtenavg float, @ labavg float

Select @ writtenavg = AVG (writtenexam), @ labavg = AVG (labexam) from stumarks

Print 'average test score: '+ convert (varchar (5), @ writtenavg)

Print 'average server trial score: '+ convert (varchar (5), @ labavg)

If (@ wirttenavg> 70 and labavg> 70)

Print 'course score: Excellent'

Else

Print 'course score: Poor'

Select stuname, stuinfo. stuno, writtenexam, labexam fomr stuinfo innner join stumarks on stuinfo. stuno = stumarks. suno where writtenexam <60 or stuexam <60

Go

Exec proc_stu

 

(2) create a stored procedure with input parameters

Use studb

Go

If exists (select * From sysobjects where name = 'proc _ stu ')

Drop procedure proc_stu

Go

Create procedure proc_stu

@ Writtenpass Int = 60,

@ Labpass Int = 60

As

Select stuname, stuinfo. stuno, writtenexam, labexam from stuinfo inner join stumarks on stuinfo. stuno = stumarks. stuno where writtenexam <@ writtenpass or labexam <@ labpass

Go

Exec proc_stu is equivalent to (Exec proc_stu 60, 60)

Exec proc_stu 64 is equivalent to (Exec proc_stu 64,60)

Exec proc_stu 60, 50

Exec proc_stu @ labpass = 55, @ writtenpass = 65 is equivalent to (Exec proc_stu 65,55)

(3) create a stored procedure with output parameters

Use studb

Go

If exists (select * From sysobjects where name = 'proc _ stu ')

Drop procedure proc_stu

Go

Create procedure proc_stu

@ Notpasssum in output,

@ Writtenpass Int = 60,

@ Lapass Int = 60

As

Select stunam, stuinfo. stuno, writtenexam, labexam from stuinfo inner join stumarks on stuinfo. stuno = stumarks. stuno where writtenexam <@ writtenpass or labexam <@ labpass

Select @ notpasssum = count (stuno) from stumarks where writtenexam <@ writtenpass or labexam <@ labpass

Go

Declare @ sum int

Exec proc_stu @ sum output, 64

If

@ Sum> = 3

Print 'number of failed users: '+ convert (varchar (5), @ sum) +' person'

Else

Print 'number of people with deflation: '+ convert (carchar (5), @ sum) +' person'

Go