SQL Server variable if,while, stored procedure

A. Variable

1.if Cycle

2.

3.while Cycle

declare @ss int
set @ss =2
begin
print ' Hello '
set @[email protected]+1
end
--break jump out of the loop

declare @sss int
set @sss =2
while @sss <10
begin
print ' Hello '
set @[email protected]+1
break
end
--continue jumps out of the loop and continues the next loop

DECLARE @ssss int
Set @ssss =2
While @ssss <10
Begin
Set @[email protected]+1
If @ssss >4and @ssss <7
Continue
print ' Hello '
End

4. Practice, review the process of looping

5.case When--equivalent if but limited, requires a specific value
--Check the age of the teacher's watch, if >=35, "Old Man"
--<=30 "Teenagers"
--Others, "just the same year"
Select Age,
Case Age
When the "Old Man"
When the "Old Man"
When Panax Notoginseng then ' old man '
When the "Old Man"
When the ' teenagers '
When the "teenagers"
When the ' teenagers '
When the "teenagers"
Else ' is that year '
End
From teacher

6. Query the gender of the student with the highest score in English, if male, "This is a boy"
--If the female, "This is a little girl"
--If the English score is more than 90, "very good results, continue to stay"
declare @xing char (TEN)
Select @xing =sex from students where code = (select top 1 code from score order BY Yingfen Desc)
declare @yingfen int
if @xing = ' Male '
print ' This is a boy '
else if @xing = ' female '
print ' This is a little girl '
else if @yingfen >90
print ' score is very good, continue to hold '

7. Query the name and subject of the instructor of the student with the highest mathematics score,
--"* * teachers teach high quality, is a * * teacher!" 】
DECLARE @jname varchar (50)
Declare @lesson char (10)
Select @jname =name from teacher where code=
(select Shujiao from Student where code=
(select top 1 code from score order BY Shufen Desc))

Select @lesson =lesson from teacher where name [email protected]
Print @jname + ' teacher quality high ' + ' is a ' +rtrim (@lesson) + ' teacher '

7. Enquiries on the work number, name and subject of the three classes of language teachers
Select Code,lesson,name from teacher where code= (select top 1 yujiao from student where banji= ' class three ' order by Yujiao)

8. Find out all the information of the language teachers of the students with the highest scores
SELECT * FROM teacher where code =
(select Yujiao from student where code =
(select top 1 code from score Group by Code order by SUM (Shufen+yufen+yingfen) desc))

Two. Storage

1.create proc firstproc--Create a stored procedure
As--Stored procedure keywords
The statement of the SELECT * from student--stored procedure
Go
--a statement that executes the stored procedure (two can be)
EXEC Firstproc
Execute Firstproc

--Stored procedures can have return values
--Define a variable to receive
DECLARE @fanhui int
EXEC @fanhui = firstproc--need to be executed before there is a return value, received with a variable
Select @fanhui as return value-view return value

--Modify the statement of the stored procedure
ALTER PROC Firstproc
As
SELECT * FROM Score
Go

exercise: Using stored procedures to find scores of students in a language teacher Zhang Xiaohua course
Over 80 of the outstanding, the outstanding number of more than 3 people is "Teacher Evaluation standards"
if less than 3 people, "substandard"
CREATE proc Thirdproc
as
declare @code int
Select @code =code from teacher where name = ' Zhang Xiaohua '
declare @count int
Select @count =count (*) from score where code in (select code from students where Yujiao [email protected]) and yufen& Gt;80
if @count >3
print ' Teacher Evaluation standards '
Else
print ' Teacher evaluation not standard '
Go
exec Thirdproc

2.

3.

exercise: Stored Procedures
Check whether the student entered the number can be completed, two or more passes can be graduated
all of them, "excellent."
two passes, "graduation"
one pass, "no completion"
all of them failed, "please rebuild."
CREATE proc Sevenproc
@code int
as
declare @yufen decimal (18,2), @shufen decimal (18,2), @yingfen decimal (18,2)
Select @yufen =yufen from score where code [email protected] to query the score of English
Select @shufen =shufen from score where code [email protected]
Select @yingfen =yingfen from score where code [email protected]
declare @count int--defining tag Variables
Set @count = 0-the tag variable needs to be re-assigned first, so give it 0 first
if @yufen >=60--judging the number of words in English
set @[email protected]+1--pass the time count+1
if @shufen >=60
Set @[email protected]+1
if @yingfen >=60
Set @[email protected]+1

If @count = 3--judge the value of Count: Judge a few courses pass
print ' excellent '
else if @count =2
print ' Graduation '
else if @count =1
print ' Do not graduate '
Else
print ' Please rebuild '
Go
--Execution
EXEC sevenproc 4

4. Stored procedure with return value without parameters
Create proc Elevenproc
As
Return 5
Go
-Execution
--Requires a variable to receive the return value
DECLARE @fan int
EXEC @fan =elevenproc
Print @fan

5. Stored procedure with parameters, with return value
create proc Twelveproc
@one int,
@two int
as
declare @sum int
set @[email protected][email  protected]
return @sum
go
--execute
declare @fanhuizonghe int
exec @fanhuizonghe = Twelveproc 2,4
print @fanhuizonghe

Exercise: Enter a student's number and want to go through the stored procedure to get the total score in this student
CREATE proc Thirteenproc
@code int
as
declare @yu decimal (18,2), @shu decimal (18,2), @ying decimal (18,2)
Select @yu =yufen from score where code [email protected]
Select @shu =shufen from score where code [email protected]
Select @ying =yingfen from score where code [email protected]
declare @sum int
Select @[email protected][email protected][email protected]
return @sum
Go

DECLARE @fan int
EXEC @fan =thirteenproc 5
Print @fan

SQL Server variable if,while, stored procedure