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