如何在存储过程中使用like操作符
目标:
要查询数据库中的一个操作日志表,其中要根据日志内容进行筛选,比如要查询所有日志内容中有 添加 字样的记录;
代码:
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
这里的关键还是 两个单眼号的连续使用,注意观察
1
if (@Description!=null)
2
set @s=@s+' Description like ''%'+@Description+'%'''
这里%旁边的是两个单眼号,而不是双眼号;![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
注意了这点就ok了,其他的没什么好说的;
祝你成功