sql批量删除和条件查询

 

ALTER   PROCEDURE   [ dbo ] . [ PublishYearly_Delete ]
     @in_YearlyID   VARCHAR ( 500 )
AS

BEGIN
     UPDATE  PublishYearly  SET  Status = 2   WHERE   CHARINDEX ( ' , ' + CAST (YearlyID  AS   VARCHAR ( 100 )) + ' , ' , @in_YearlyID ) > 0
END

ALTER   PROCEDURE   [ dbo ] . [ PublishYearly_GetList ]
     @request_page_num   INT , -- 基于0的，如第一页@page_size=0
     @page_size   INT ,
     @Year   INT ,
     @Status   INT ,
     @PublishDate   DATETIME ,
     @ExpiredDate   DATETIME
AS
BEGIN
     DECLARE   @sql   NVARCHAR ( Max )
     SET   @sql = ' SELECT *,ROW_NUMBER() OVER (ORDER BY YearlyID) AS RowNumber FROM PublishYearly WHERE 1=1 '
    
     IF ( @Year != 0 )
         SET   @sql = @sql + '  AND Year= ' + CAST ( @Year   AS   VARCHAR ( 10 ));
        
     IF ( @Status != 0 )
         SET   @sql = @sql + '  AND Status= ' + CAST ( @Status   AS   VARCHAR ( 10 ));
        
     IF ( @PublishDate   IS   NOT   NULL )
         SET   @sql = @sql + '  AND PublishDate>= ''' + CONVERT ( VARCHAR ( 20 ), @PublishDate , 120 ) + '''' ;
        
     IF ( @ExpiredDate   IS   NOT   NULL )
         SET   @sql = @sql + '  AND ExpiredDate<= ''' + CONVERT ( VARCHAR ( 20 ), @ExpiredDate , 120 ) + '''' ;
        
     PRINT   ( ' SELECT COUNT(*) FROM ( ' + @sql + ' ) _temp ' )

     EXEC  ( ' SELECT COUNT(*) FROM ( ' + @sql + ' ) _temp ' )
    
     SET   @sql = ' SELECT *,(CASE Status WHEN  '' 1 ''  THEN  '' 正常 ''  ELSE  '' 无效 ''  END) AS StatusN FROM (SELECT * FROM ( ' +   @sql + N ' ) _temp WHERE RowNumber BETWEEN  ' + CAST ( @request_page_num * @page_size + 1   AS   VARCHAR ( 10 )) + '  AND  ' + CAST (( @request_page_num + 1 ) * @page_size   AS   VARCHAR ( 10 )) + ' ) tt  '
    
     PRINT  ( @sql )
     EXEC  ( @sql )

END

 

转载于:https://www.cnblogs.com/stalwart/archive/2011/04/02/2003658.html