历经千辛万苦，终于实现了这个组合查询的存储过程。之前在网上看了很多资料，感觉讲的也就那么回事儿。51Testing软件测试网-VE
U{;k2Tu;|,s;Z 在写存储过程之前，心中总是有很多疑虑。比如创建好存储过程之后，保存在哪里才会不妨碍它运行呢？我刚写完的存储过程，在SQLServer里对应的位置为什么不显示呢？存储过程的名字怎样起呢？…………51Testing软件测试网`3NR(oLk8kiJ @!_
51Testing软件测试网,]"z-c9W
OwX 可能就是对这块儿的知识太匮乏了，才会产生诸多疑虑。但是只要你自己动手把它实现了，哪怕是简单功能的实现，都会切身体会到”实践出真知”深刻内涵。51Testing软件测试网z6H#F ?l[:L/xX@
| 51Testing软件测试网*Q wA8v/gF"W set ANSI_NULLS ON51Testing软件测试网9?/z'ZO,]_]FE(v
set QUOTED_IDENTIFIER ON
k|V V-k|;IQ1dY0go (p4m7Wpp051Testing软件测试网(M0?"g k p8T m
7k$rIDk!C0ALTER PROCEDURE [dbo].[OperRecordThree]51Testing软件测试网-m,D&C;B~ iuH
mG X
--这里定义变量，可以通过外界依次传入参数，与变量匹配
5x F9?X5s KYE r5w0 @File varchar(10), --字段0
^Jq yk}0 @Operator varchar(2),--操作符0
UZ;Ou kj0 @Context varchar(10),--内容0
i-JN/^D(p q0
h&Z;~!B#\-J0 @RelationOne varchar(3),--关系1
q+`e}:D2ee0 @FileOne varchar(10), --字段1
](z0E;UX,\*C$M0 @OperatorOne varchar(2), --操作符151Testing软件测试网0x&d%R.K
jX&Y
@ContextOne varchar(10),--内容1
"R\ p`g0 51Testing软件测试网F;v TN [6X
@RelationTwo varchar(3),--关系2
e(D/vE*Q-so0 @FIleTwo varchar(10), --字段251Testing软件测试网u6Z%J _5T h*n
@OperatorTwo varchar(2),--操作符251Testing软件测试网/_$S f;{4t-fD
@ContextTwo varchar(10)--内容251Testing软件测试网
?|i v z
AS51Testing软件测试网!u+?H w1j
--这里定义临时变量51Testing软件测试网 u Zqy6E n
DECLARE @TempSql varchar(500)
[7B.?
qC)s0BEGIN
b3y ~Ia;{^+?0 --char(32)是空格，char(39)是单引号
Dx(@k-U
H0 set @TempSql='select * from workingTeacher where '+char(32)51Testing软件测试网N5U Tw)Y n
+@File+@Operator+char(39)+@Context+char(39)
wD(s,V9s])v \&`c"Gs0+char(32)+@RelationOne+char(32)
_FB/H [.|/h0+@FileOne+@OperatorOne+char(39)+@ContextOne+char(39)51Testing软件测试网,W)o p#X,D mNb#r o||R
+char(32)+@RelationTwo+char(32)
iD9]1tG%m%l&j]0+@FIleTwo+@OperatorTwo+char(39)+@ContextTwo+char(39)51Testing软件测试网9B ML O)x
--下面这一句是执行这个存储过程的语句。
'H-B'N7Tc s*Os'Q[0 execute (@tempsql)
~ M.Qs7~F"i&\3D0END51Testing软件测试网R$N$n N ?E S
|
E?ri,M0 测试语句：
8d6y d P e X0| execute OperRecordThree 'robot','<>','3','and','levels','<>','管理员','and','robot','<>','2' |
q y i0mBFGZ0 下面是DAL层的调用函数：51Testing软件测试网lj.G2eG*J%[O
9b\ l j Ae0#Region "操作员工作纪律第三排记录"51Testing软件测试网;w"J:w{
G
g
Public Function OperatorRecordThree(ByVal OperatorRec() As Entry.StuBaseInfoInq) As DataTable51Testing软件测试网 w^(q!q*a9D3n
Dim sql As String = "OperRecordThree"51Testing软件测试网8IW"? b0j1Fh
Dim sqlcmd As SqlCommand = New SqlCommand(sql, con)51Testing软件测试网y NV}grO,bF
Dim dt As New DataTable
hC1Yl@4A _0 Dim dst As New DataSet51Testing软件测试网$D T6A!g O y_.T&p
Dim MyDataAdapter As New SqlDataAdapter
I5W/l4LCD1U0 sqlcmd.CommandType = CommandType.StoredProcedure
X^ K ri0 '依次为存储过程的参数赋值51Testing软件测试网W `;i
s h a
sqlcmd.Parameters.Add("@File", SqlDbType.VarChar, 10).Value = OperatorRec(0).Field
E
TuVp:Y
R0 sqlcmd.Parameters.Add("@Operator", SqlDbType.VarChar, 2).Value = OperatorRec(0).Oper51Testing软件测试网$a&X%y6uikm
sqlcmd.Parameters.Add("@Context", SqlDbType.VarChar, 10).Value = OperatorRec(0).Context
M5I Hf%p d$j \ Q,qU051Testing软件测试网8P.j r2DGf sqlcmd.Parameters.Add("@RelationOne", SqlDbType.VarChar, 3).Value = OperatorRec(1).Relation
4y5SBaqG4|X;W8M*h*O0 sqlcmd.Parameters.Add("@FileOne", SqlDbType.VarChar, 10).Value = OperatorRec(1).Field51Testing软件测试网n^(r*F,\%SX
sqlcmd.Parameters.Add("@OperatorOne", SqlDbType.VarChar, 2).Value = OperatorRec(1).Oper51Testing软件测试网BX(VvM E Z+XI
sqlcmd.Parameters.Add("@ContextOne", SqlDbType.VarChar, 10).Value = OperatorRec(1).Context
4y`8upVO~0'pM#v z#cMLJ0 sqlcmd.Parameters.Add("@RelationTwo", SqlDbType.VarChar, 3).Value = OperatorRec(2).Relation
)Y3hE Ej w)w C6w9G0 sqlcmd.Parameters.Add("@FileTwo", SqlDbType.VarChar, 10).Value = OperatorRec(2).Field51Testing软件测试网2l D o:o~V7yZ
sqlcmd.Parameters.Add("@OperatorTwo", SqlDbType.VarChar, 2).Value = OperatorRec(2).Oper51Testing软件测试网2n8IL ~x akO
sqlcmd.Parameters.Add("@ContextTwo", SqlDbType.VarChar, 10).Value = OperatorRec(2).Context
!Rxr-Kqy%^051Testing软件测试网'Nm)q8S#ww0E MyDataAdapter.SelectCommand = sqlcmd51Testing软件测试网 x-q\S,l,]9e5X3J
MyDataAdapter.Fill(dst, "workingTeacher")51Testing软件测试网V/P%f)Gw
dt = dst.Tables("workingTeacher")
)qI-z7W.gm#T0 con.Open()
#D].C8PX3K:X0 sqlcmd.ExecuteNonQuery().ToString()51Testing软件测试网5E2i(c_mV
Return dt
0C2c~+o1N0 con.Close()51Testing软件测试网5XH0gj
H tJQ$d
End Function
9F R6_ l3JW|vu-h;r0#End Region51Testing软件测试网+{Q G B&Kz
