参考学习 

 1 --函数判断字符含5个以上字符串和2个以上数字
 2 
 3 /****** Object:  UserDefinedFunction [dbo].[funISMEMBER]    Script Date: 09/13/2017 09:40:40 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 ALTER FUNCTION [dbo].[funISMEMBER] (@Minfo VARCHAR(255)) RETURNS INT
 9 BEGIN
10    DECLARE @i INT
11    DECLARE @result INT
12    Declare @ccount int
13    Declare @ncount int
14    SET @ccount = 0
15    SET @ncount = 0
16    SET @i=1
17    SET @result=1
18    WHILE @i <= LEN(@Minfo)
19      BEGIN
20         IF(SUBSTRING(@Minfo,@i,1) not like '[A-Z]' AND SUBSTRING(@Minfo,@i,1) not like '[a-z]' AND SUBSTRING(@Minfo,@i,1) not like '[0-9]'
21             and len(SUBSTRING(@Minfo,@i,1))<DATALENGTH(SUBSTRING(@Minfo,@i,1))  )
22           BEGIN
23             SET @ccount=@ccount+1
24           END
25          IF(SUBSTRING(@Minfo,@i,1)  like '[0-9]')
26           BEGIN
27             SET @ncount=@ncount+1
28           END 
29           
30        SET @i=@i+1
31      END
32      
33      if @ccount>=5 and @ncount>=2
34         begin
35             RETURN '1'
36         end
37      else
38         begin
39             RETURN '0'
40         end
41         RETURN '0'
42 END
View Code
 1 --存储过程参考格式
 2 
 3 SET ANSI_NULLS ON
 4 GO
 5 SET QUOTED_IDENTIFIER ON
 6 GO
 7 
 8 CREATE Procedure [dbo].[FRReport_Rpt_Name]
 9 @UserName nvarchar(50),
10 @SaleDateBegin VARCHAR(10),
11 @SaleDateEnd VARCHAR(10),
12 @CounterID VARCHAR(1000),
13 @MemID VARCHAR(1000)
14 As
15 
16 --调用函数
17 DECLARE @CustomerId varchar(50)
18 execute @CustomerId=dbo.getCustomerId
19 
20 DECLARE @HDKDB_USER VARCHAR(100)
21 SET @HDKDB_USER=dbo.getHDKDB('USER')
22 
23 DECLARE @HDKDB_MEMBER VARCHAR(100)
24 SET @HDKDB_MEMBER=dbo.getHDKDB('MEMBER')
25 
26 DECLARE @HDKDB_ORDER VARCHAR(100)
27 SET @HDKDB_ORDER=dbo.getHDKDB('ORDER')
28 
29 Declare @StrWhere VARCHAR(8000)
30 Declare @Sql VARCHAR(8000)
31 Set @StrWhere=''
32 
33 --柜台条件拼接
34 If ISNULL(@CounterID,'')!=''
35 Begin
36  Set @strWhere=@strWhere+' and (og.OrgLevelCode_5 in('+@CounterID+') 
37  or og.OrgLevelCode_4 in('+@CounterID+')
38  or og.OrgLevelCode_3 in('+@CounterID+')
39  or og.OrgLevelCode_2 in('+@CounterID+')
40  or og.OrgLevelCode_1 in('+@CounterID+'))'
41 End
42 If ISNULL(@MemID,'')!=''
43 Begin
44  Set @strWhere=@strWhere+' and (mm.number like ''%'+@MemID+'%'' or mm.mobile like ''%'+@MemID+'%'' or mm.name like ''%'+@MemID+'%'')'
45 End
46 
47 Set @Sql=''
48 Print(@Sql)
49 Exec(@Sql)
View Code

 

posted @ 2017-09-13 10:16  wang_ying  阅读(149)  评论(0)  编辑  收藏  举报