这是一个例子 可以看看
--员工基本信息
-----------------------------查询/搜索
IF EXISTS(SELECT name FROM sysobjects WHERE name='PROC_SELECTempGeneralBYTEMP')
DROP PROC PROC_SELECTempGeneralBYTEMP
go
CREATE PROC PROC_SELECTempGeneralBYTEMP
@strWhere VARCHAR(5000) = ' '--查询条件
as
declare @strSQL VARCHAR(5000)--主语句
SET @strSQL=
'SELECT A.ID AS ID,A.empID AS empID,A.empCName AS empCName,A.empEName AS empEName,A.deptID as deptID,
(B.deptName+''-''+B.deptDescription) AS deptName,D.CodeCaption AS SEX,C.empSex AS empSexID,
A.workcycleID AS workcycleID,A.workFromDate AS workFromDate,E.workcycleName AS workcycleName,
A.posID AS posID,F.posName AS posName,C.empIDCard AS empIDCard,
A.posStatus AS posStatusID,G.CodeCaption AS posStatus,H.CodeCaption AS isCountIn,
I.CodeCaption AS isCalcForOvertime,A.coFromDate AS coFromDate,
A.prepareLeaveDate AS prepareLeaveDate,A.leaveDate AS leaveDate,
A.empClass AS empClassID,J.CodeCaption AS empClass,
A.empDescription AS empDescription
FROM empGeneral A
INNER JOIN departments B ON A.deptID=B.deptID
--部门表
INNER JOIN empBaseinfo C ON A.empID=C.empID
--员工信息表
INNER JOIN hrsysCodes D ON C.empSex=D.CodeValue--性别表
INNER JOIN wtWorkcycles E ON A.workcycleID=E.workcycleID--班序表
INNER JOIN positions F ON A.posID=F.posID--职位表
INNER JOIN hrsysCodes G ON A.posStatus=G.CodeValue--在职情况
INNER JOIN hrsysCodes H ON A.isCountIn=H.CodeValue--进行每日考勤
INNER JOIN hrsysCodes I ON A.isCalcForOvertime=I.CodeValue--是否记加班
INNER JOIN hrsysCodes J ON A.empClass=J.CodeValue--员工类型
WHERE D.CodeClass=''empSex'' and G.CodeClass=''posStatus'' and H.CodeClass=''clearItNextGeting''
and I.CodeClass=''clearItNextGeting'' and J.CodeClass=''empClass''' +@strWhere+' ORDER BY ID'
EXEC(@strSQL)
go
EXEC PROC_SELECTempGeneralBYTEMP @strWhere=''
-----批量删除时不仅要把员工基本信息表中的信息删除，还需要把信息表empBaseInfo表也删除
IF EXISTS(SELECT name FROM sysobjects WHERE name='PROC_DELETEGeneralBYEMPID')
DROP PROC PROC_DELETEGeneralBYEMPID
go
CREATE PROC PROC_DELETEGeneralBYEMPID
@empID varchar(500)
AS
DELETE empGeneral WHERE empID=@empID
DELETE empBaseinfo WHERE empID=@empID
GO
EXEC PROC_DELETEGeneralBYEMPID @empID=''
2010-03-28 15:41
