目标:
要查询数据库中的一个操作日志表,其中要根据日志内容进行筛选,比如要查询所有日志内容中有 添加 字样的记录;
代码:
1

SET
QUOTED_IDENTIFIER
ON
2

GO
3

SET
ANSI_NULLS
OFF
4

GO
5

if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
‘
[dbo].[GetOperationLogs]
‘
)
and
OBJECTPROPERTY
(id, N
‘
IsProcedure
‘
)
=
1
)
6

drop
procedure
[
dbo
]
.
[
GetOperationLogs
]
7

GO
8

9

10


/**/
/*
查询Voltage_Dist_Data的存储过程
*/
11

create
procedure
GetOperationLogs
12

(
13

@useridList
varchar
(
500
),
14

@BeginTime
datetime
,
15

@EndTime
datetime
,
16

@description
varchar
(
500
)
17

)
18

as
19

begin
20

declare
@s
varchar
(
2000
)
21

set
@s
=
‘
select * from d_lg_6
‘
22


/**/
/*
如果没有设置任何查询条件,在返回所有的operationlogs
*/
23

if
((
@useridList
=
null
)
and
(
@BeginTime
=
null
)
and
(
@EndTime
=
null
)
and
(
@Description
=
null
))
24

begin
25

exec
(
@s
)
26

return
27

end
28

set
@s
=
@s
+
‘
where
‘
29


/**/
/*
如果设置了useridList,则返回这些用户的OperationLogs
*/
30

if
(
@useridList
!=
null
)
31

set
@s
=
@s
+
‘
userid in (
‘
+
@useridList
+
‘
) and
‘
32


/**/
/*
如果设置了查询时间,则返回该时间那的查询时间
*/
33

if
((
@BeginTime
!=
null
)
and
(
@EndTime
!=
null
))
34

set
@s
=
@s
+
‘
logtime between
”’
+
convert
(
varchar
(
19
),
@BeginTime
,
120
)
+
”’
and
”’
+
convert
(
varchar
(
19
),
@endtime
,
120
)
+
”’
and
‘
35


/**/
/*
如果设置了日志内容过滤器,则过滤日志内容
*/
36

if
(
@Description
!=
null
)
37

set
@s
=
@s
+
‘
Description like
”
%
‘
+
@Description
+
‘
%
”’
38

if
(
substring
(
@s
,
len
(
@s
)
–
2
,
3
)
=
‘
and
‘
)
39

set
@s
=
substring
(
@s
,
0
,
len
(
@s
)
–
3
)
40

exec
(
@s
)
41

—
select @s
42

—
select substring(@s,len(@s)-2,3)
43

end
44

GO
45

SET
QUOTED_IDENTIFIER
OFF
46

GO
47

SET
ANSI_NULLS
ON
48

GO
这里的关键还是 两个单眼号的连续使用,注意观察
1

if
(
@Description
!=
null
)
2

set
@s
=
@s
+
‘
Description like
”
%
‘
+
@Description
+
‘
%
”’
这里%旁边的是两个单眼号,而不是双眼号;
注意了这点就ok了,其他的没什么好说的;
祝你成功