27,580
社区成员
发帖
与我相关
我的任务
分享
CREATE PROCEDURE [dbo].[getInfo]
(
@bumen nvarchar(20),
@jilu nvarchar(20),
@bianhao nvarchar(5),
@xianxiang13 varchar(2),
@datetime datetime,
@chuli datetime
)--WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON;
declare @searchstr nvarchar(200)
set @searchstr='select * from tablename where '
if @bumen<>'全部'
set @searchstr=@searchstr+'提交部门='''+@bumen+''' and '
if @jilu<>'全部'
begin
if @jilu='办理完毕'
set @searchstr=@searchstr+'(状态类型=''o'' or 状态类型=''w'') and '
else
set @searchstr=@searchstr+'(状态类型<>''o'' and 状态类型<>''w'') and '
end
if @bianhao<>'全部'
set @searchstr=@searchstr+'编号='+@bianhao+' and '
if @xianxiang13<>'y' or @xianxiang13<>'Y'
set @searchstr=@searchstr+convert(varchar(10),@datetime,120)+'>=开始日期 and '+convert(varchar(10),@datetime,120)+'<结束日期'
else
set @searchstr=@searchstr+'最后工作时间>=开始日期 and 最后工作时间<结束日期'
if right(@searchstr,5)=' and '
set @searchstr=left(@searchstr,len(@searchstr)-5)
if right(@searchstr,7)=' where '
set @searchstr=left(@searchstr,len(@searchstr)-7)
exec (@searchstr)
END