存储过程

PROCEDURE [dbo].[P_ActivityInfo_Select_ActivityBuy]
(
 @AI_ActivityID bigint=null,
 @AI_ActivityName nvarchar(50)=null,
 @AI_ActivityType int = null,
 @AI_ActivityStartTime  datetime = null,
 @AI_ActivityEndTime  datetime = null,
 @AI_PublishPlatform nvarchar(50) =null,
 @APP_BeginnerURL nvarchar(500) =null,
 @AI_StatusType int = null ,
 @TU_Nickname nvarchar(20) = null,
 @AI_PublishType int =null
)
AS
BEGIN
 SET NOCOUNT ON
 
 DECLARE @sql nvarchar(1000)
 
 SET @sql='SELECT  *  FROM ActivityInfo  '
 SET @sql= @sql + 'INNER JOIN ActivityFeeType ON ActivityFeeType.AFT_FeeType = ActivityInfo.AI_ActivityFeeType ';
 SET @sql= @sql + 'INNER JOIN ActivityType ON ActivityType.AT_ActivityType = ActivityInfo.AI_ActivityType ';
 SET @sql= @sql + 'INNER JOIN ActivityStatusType ON ActivityStatusType.AST_StatusType = ActivityInfo.AI_StatusType ';
 SET @sql= @sql + 'INNER JOIN TeamInfo ON TeamInfo.TI_TeamID = ActivityInfo.AI_TeamID ';
 SET @sql= @sql + ' WHERE (1=1) ';
 IF(@AI_ActivityID IS NOT NULL)
 BEGIN
  SET @sql = @sql + ' AND AI_ActivityID ='+CONVERT(nvarchar,@AI_ActivityID)+' ';
 END
 IF(@AI_ActivityName IS NOT NULL)
 BEGIN
  SET @sql = @sql + ' AND AI_ActivityName LIKE ''%'+@AI_ActivityName+'%'' ';
 END
 IF(@AI_ActivityType IS NOT NULL)
 BEGIN
  SET @sql = @sql + ' AND AI_ActivityType = '+CONVERT(nvarchar,@AI_ActivityType)+' ';
 END
 IF(@AI_ActivityStartTime IS NOT NULL)
 BEGIN
  SET @sql = @sql + ' AND AI_ActivityStartTime >= '''+CONVERT(nvarchar,@AI_ActivityStartTime)+''' ';
 END
 IF(@AI_ActivityEndTime IS NOT NULL)
 BEGIN
  SET @sql = @sql + ' AND AI_ActivityEndTime <= '''+CONVERT(nvarchar,@AI_ActivityEndTime)+''' ';
 END
 IF(@AI_PublishPlatform IS NOT NULL)
 BEGIN
  SET @sql =@sql +' AND (AI_ActivityID IN (SELECT APP_ActivityID FROM ActivityPublishPlatform  ';
  SET @sql =@sql +'WHERE APP_Name1 LIKE ''%'+ @AI_PublishPlatform + '%'' OR APP_Name2 LIKE ';
  SET @sql =@sql +'''%' + @AI_PublishPlatform + '%'' OR  APP_Name3 LIKE ''%' +@AI_PublishPlatform  +'%''))';
 END
 IF(@APP_BeginnerURL IS NOT NULL)
 BEGIN
  SET @sql =@sql +' AND (AI_ActivityID IN (SELECT APP_ActivityID FROM ActivityPublishPlatform  ';
  SET @sql =@sql +'WHERE APP_BeginnerURL LIKE ''%'+ @APP_BeginnerURL + '%'' )) ';
 END
    IF(@AI_StatusType IS NOT NULL)
 BEGIN
  SET @sql =@sql +' AND (AI_StatusType =' + CONVERT(nvarchar,@AI_StatusType) + ')';
 END
 IF(@TU_Nickname IS NOT NULL)
 BEGIN
  SET @sql =@sql +' AND (AI_ExecutorUserID IN (SELECT TU_UserID FROM TeamUser WHERE TU_Nickname ';
  SET @sql =@sql +' LIKE ''%'+@TU_Nickname+ '%'' )) ';
 END
 IF(@AI_PublishType IS NOT NULL)
 BEGIN
  SET @sql =@sql +' AND (AI_PublishType =' + CONVERT(nvarchar,@AI_PublishType) + ')';
 END

 EXEC(@sql);

END