方法一:
关于日期的命名
@DTBegin DATETIME=NULL,
@DTEnd DATETIME=NULL,
按照时间进行查询
AND
(@DTBegin IS NULL OR DATEDIFF ( dd ,@DTBegin ,T_CM_SubscribeInfo.ApplyTime) >= 0 )
AND
(@DTEnd IS NULL OR DATEDIFF ( dd ,T_CM_SubscribeInfo.ApplyTime, @DTEnd ) >= 0 )
方法二:
在最初设定好默认时间。
IF @BeginDate IS NULL
SET @BeginDate = '1980-1-1'
ELSE
SELECT @BeginDate = CONVERT(char(11),@BeginDate ,120) + '00:00:00'
IF @EndDate IS NULL
SET @EndDate = '9998-1-1'
ELSE
SELECT @EndDate = CONVERT(char(11),@EndDate ,120) + '23:59:59:998'
在后面进行调用
AND ((@BeginDate IS NULL AND @EndDate IS NULL) OR ( S.declareDate BETWEEN @BeginDate AND @EndDate))
以某字符分割字符串(分割字符串函数)
CREATE FUNCTION F_BD_SplitString_Query
(
@StrExpression VARCHAR(MAX),
@StrSplit VARCHAR(32)
)
RETURNS @Table Table(col VARCHAR(32))
AS
BEGIN
WHILE(CHARINDEX(@StrSplit,@StrExpression)<>0)
BEGIN
IF(SUBSTRING(@StrExpression,1,CHARINDEX(@StrSplit,@StrExpression)-1) IS NOT NULL
AND LTRIM(RTRIM(SUBSTRING(@StrExpression,1,CHARINDEX(@StrSplit,@StrExpression)-1)))<>'' )
INSERT INTO @Table(col) VALUES (SUBSTRING(@StrExpression,1,CHARINDEX(@StrSplit,@StrExpression)-1))
SET @StrExpression = STUFF(@StrExpression,1,CHARINDEX(@StrSplit,@StrExpression),'')
END
IF(@StrExpression IS NOT NULL AND LTRIM(RTRIM(@StrExpression)) <> '')
INSERT INTO @Table(col) VALUES (@StrExpression)
RETURN
END
调用函数:
CREATE PROCEDURE P_EmployeeInfoForPosition_Query
@OperatorPointCode VARCHAR(32) = NULL, -- 操作者所在网点号
@OperatorCode VARCHAR(32), -- 操作码
@EmployeePosition VARCHAR(128)=null, -- 职位信息
@RetrieveCode VARCHAR(32)=NULL,--2012-6-3 肖建鹏添加
@Stars int = NULL
AS
BEGIN
-- 职位信息参数
DECLARE @PositionInfo VARCHAR(Max)
DECLARE @Spliter VARCHAR(32)
SET @PositionInfo = dbo.F_GetSystemParam(@OperatorPointCode,@OperatorCode,@EmployeePosition) --系统参数获取职位信息
SET @Spliter = ';'
-- 员工信息查询
SELECT
tei.EmployeeCHName AS 中文名称,
tei.EmployeeCode AS 工号
FROM T_EmployeeInfo tei
WHERE (tei.EmployeePosition IN (SELECT Col FROM dbo.F_BD_SplitString_Query(@PositionInfo,@Spliter)) OR @EmployeePosition IS NULL OR @EmployeePosition = '') --如果职位为空的话,就查询出网点的所有员工
AND tei.PointCode = @OperatorPointCode
AND (@Stars is null or Stars =@Stars)
AND (Status = 1)
AND (@RetrieveCode IS NULL OR @RetrieveCode ='' OR tei.EmployeeCHName LIKE '%'+@RetrieveCode+'%' OR tei.EmployeeSpellingCode LIKE '%'+@RetrieveCode+'%')--通过姓名和拼音简码搜索员工
END