27,579
社区成员
发帖
与我相关
我的任务
分享
ALTER PROCEDURE [dbo].[HomeAdmin_TaskProgress_SelectAllTaskBySomeCondition_p]
@TaskListId INT
,@TaskContent NVARCHAR(100)=NULL
,@TypeId INT=0
,@PlannerId INT =0
,@TransactorId INT =0
,@FinishState INT =0
,@FinishTime DATETIME =NULL
,@OrderByName NVARCHAR(50)
,@OrderBy NVARCHAR(10)
AS
BEGIN
DECLARE @strSql nvarchar(2000)
set @strSql='
SELECT
TaskId,
TaskListId,
TaskTitle,
TaskContent,
TypeId,
PlannerId,
TransactorId,
FinishState,
FinishTime,
[Plan],
Art,
Cooperation,
Youxian,
CreateAdministratorID,
CreateAdministratorName,
CreateTime
FROM TaskProgress_Task_t
WHERE [DISABLE]=0 AND 1=1 AND TaskListId='+cast(@TaskListId as varchar(10))+'
AND ('+@TaskContent+' IS NULL OR TaskContent LIKE ''%'+@TaskContent+'%'')
AND ('+cast(@TypeId as varchar(10))+'=0 OR TypeId='+cast(@TypeId as varchar(10))+')
AND ('+cast(@PlannerId as varchar(10))+'=0 OR PlannerId='+cast(@PlannerId as varchar(10))+')
AND ('+cast(@TransactorId as varchar(10))+'=0 OR TransactorId='+cast(@TransactorId as varchar(10))+')
AND ('+cast(@FinishState as varchar(10))+'=0 OR FinishState='+cast(@FinishState as varchar(10))+')
AND ('+cast(@FinishTime as varchar(20))+' IS NULL OR FinishTime='+cast(@FinishTime as varchar(10))+')
ORDER BY '+ @OrderByName +' '+@OrderBy
EXEC @strSql
END
('+@TaskContent+' IS NULL
AND abcd IS NULL OR TaskContent
那它就把执行的时候就是字段了或者null is NULL的话。那字符串就就不对了
set @strSql='
SELECT
TaskId,
TaskListId,
TaskTitle,
TaskContent,
TypeId,
PlannerId,
TransactorId,
FinishState,
FinishTime,
[Plan],
Art,
Cooperation,
Youxian,
CreateAdministratorID,
CreateAdministratorName,
CreateTime
FROM TaskProgress_Task_t
WHERE [DISABLE]=0 AND 1=1 '
+isnull('AND TaskListId='+cast(@TaskListId as varchar(10)),'')
@sql=' SELECT
TaskId,
TaskListId,
TaskTitle,
TaskContent,
TypeId,
PlannerId,
TransactorId,
FinishState,
FinishTime,
[Plan],
Art,
Cooperation,
Youxian,
CreateAdministratorID,
CreateAdministratorName,
CreateTime
FROM TaskProgress_Task_t
WHERE [DISABLE]=0 AND 1=1 '
if @TaskContent<>''
begin
@sql=@sql + 'TaskContent LIKE ''%'+@TaskContent+'%''
end
.
.
.
.