Process Control for SQL statements and stored procedure query statements

Author: dish

Team: freexploit

Date: 2005-07-06

Site: http://blog.csdn.net/freexploit

Drop table classname
Declare @ teacherid int
Declare @ a char (50)
Declare @ B char (50)
Declare @ C char (50)
Declare @ d char (50)
Declare @ E char (50)
Set @ teacherid = 1

Select @ A = drclass1, @ B = drclass2, @ C = drclass3, @ d = drclass4, @ E = drclass5 from teacher where teacherid = @ teacherid

Create Table classname (classname char (50 ))
Insert into classname (classname) values (@)
If (@ B is not null)
Begin
Insert into classname (classname) values (@ B)

If (@ C is not null)
Begin
Insert into classname (classname) values (@ C)

If (@ D is not null)
Begin
Insert into classname (classname) values (@ D)
If (@ E is not null)
Begin
Insert into classname (classname) values (@ E)
End
End
End
End

Select * From classname

Can these SQL statements be converted into a stored procedure? I tried it myself.
Alter procedure pr_getclass

@ Teacherid int,
@ A char (50 ),
@ B char (50 ),
@ C char (50 ),
@ D char (50 ),
@ E char (50)
As

Select @ A = drclass1, @ B = drclass2, @ C = drclass3, @ d = drclass4, @ E = drclass5 from teacher where teacherid = @ teacherid
Drop table classname
Create Table classname (classname char (50 ))

Insert into classname (classname) values (@)
If (@ B is not null)
Begin
Insert into classname (classname) values (@ B)

If (@ C is not null)
Begin
Insert into classname (classname) values (@ C)

If (@ D is not null)
Begin
Insert into classname (classname) values (@ D)
If (@ E is not null)
Begin
Insert into classname (classname) values (@ E)
End
End
End
End

Select * From classname
In this case, the stored procedure has six variables. In fact, only one variable can be provided.

The main problem is that I have not figured out that @ A, @ B, @ C, @ D, and so on are temporary variables. They are placed behind the as to re-declare them, instead of the variable definition that begins with the entire stored procedure.

The stored procedure is as follows:

Create procedure pr_getclass
@ Teacherid int
As

Declare @ a char (50), @ B char (50), @ C char (50), @ d char (50), @ E char (50)
Select @ A = drclass1, @ B = drclass2, @ C = drclass3, @ d = drclass4, @ E = drclass5 from teacher where teacherid = @ teacherid
Drop table classname
Create Table classname (classname char (50 ))
Insert into classname (classname) values (@)
If (@ B is not null)
Begin
Insert into classname (classname) values (@ B)
If (@ C is not null)
Begin
Insert into classname (classname) values (@ C)
If (@ D is not null)
Begin
Insert into classname (classname) values (@ D)
If (@ E is not null)
Begin
Insert into classname (classname) values (@ E)
End
End
End
End
Select * From classname
Go

2 join Table query

I have three tables.
KJ table
Kjid
Teacherid
........................................ ..................

Teacher table

Teacherid
Teachername
Collageid
........................................ .................

Collage table

Collageid
Collagename

I want to write an SQL statement to query all the KJ files, and find teachername Based on teacherid of kJ. Also, check collageid of teacherid and teacher to find collage, in the last generated dataset, in addition to the kjname, teachername and collagename must be added to the KJ attribute.

The statement is as follows:

Select t1.kjname, t2.teachername, t3.collagename
From kJ T1
Left join teacher T2 on t2.teacherid = t1.teacherid
Left join collage T3 on t3.collageid = t2.collageid