Sql存储过程即返回结果集又返回值以及动态创建视图(二)
end
--set @sql+=' ) as IDWithRowNumber where IDRank>'+ convert(nvarchar(10),@pageSizes )+'*'+convert(nvarchar(10),@pageIndex ) +' and IDRank<='+ convert(nvarchar(10),@pageSizes )+'*('+convert(nvarchar(10),@pageIndex ) +'+1)'
end
end
else
begin
IF LEN(@sql)=0
begin
-- set @sql=' select '''',Seq_no as 序列号,Destination_no as 发送号码,Sms_SendContents as 短信内容,Send_Status as 提交状态,Send_Date as 发送时间,Oper_Date as 操作时间,Send_Predate as 预发时间, case is_Pre when 1 then ''任务发送'' else ''非任务发送'' end as 是否任务,RealStatus as 到达状态,MtDateTime as 状态时间,id,MessageType,isnull(MmcIndex,1) as MmcIndex from (select *,Row_number() over(order by Oper_Date desc) as IDRank from B_Sms_Send_info_'+@PipeId+' where Send_user in('+@user_code+') and (Oper_Date >=convert(datetime,'''+@DateStart+' 00:00:00'',120) or ''''= '''+@DateStart+''' ) and (Oper_Date <=convert(datetime,'''+@DateEnd+' 23:59:59'',120) or ''''= '''+@DateEnd+''' ) and (Destination_no='''+@Destination_no+''' or ''''='''+@Destination_no+''') '
set @sql=' select '''' as 序号,Seq_no as 序列号,Destination_no as 发送号码,Sms_SendContents as 短信内容,Send_Status as 提交状态,Send_Date as 发送时间,Oper_Date as 操作时间,id,MessageType,isnull(MmcIndex,1) as MmcIndex from B_Sms_Send_info_'+@PipeId+' where Send_user in('+@user_code+') and (Oper_Date >=convert(datetime,'''+@DateStart+' 00:00:00'',120) or ''''= '''+@DateStart+''' ) and (Oper_Date <=convert(datetime,'''+@DateEnd+' 23:59:59'',120) or ''''= '''+@DateEnd+''' ) and (Destination_no='''+@Destination_no+''' or ''''='''+@Destination_no+''') and Send_Status!=-11 '
set @sqlCount+=' select COUNT(*) as Counts from B_Sms_Send_Info_'+@PipeId+' where Send_user in('+@user_code+') and (Oper_Date >=convert(datetime,'''+@DateStart+' 00:00:00'',120) or ''''= '+@DateStart+' ) and (Oper_Date <=convert(datetime,'''+@DateEnd+' 23:59:59'',120) or ''''= '''+@DateEnd+''') and (Destination_no='''+@Destination_no+''' or ''''='''+@Destination_no+''')and Send_Status!=-11 '
if(@Status='0')
begin
set @sql+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) '
set @sqlCount+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) '
end
if(@Status='1')
begin
set @sql+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) and Send_Status=1 '
set @sqlCount+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) and Send_Status=1 '
end
if(@Status='-1')
begin
set @sql+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) and Send_Status=-1 '
set @sqlCount+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) and Send_Status=-1 '
end
if(@Status='DELIVRD')
begin
set @sql+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) and RealStatus = ''DELIVRD'' '
set @sqlCount+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) and RealStatus = ''DELIVRD'' '
end
if(@Status='2')
begin
set @sql+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) and (RealStatus <> ''DELIVRD'' and RealStatus <> '' and RealStatus is not null) '
set @sqlCount+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) and (RealStatus <> ''DELIVRD'' and RealStatus <> '' and RealStatus is not null) '
end
if(@Status='NULL')
begin
set @sql+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) and RealStatus is null '
set @sqlCount+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) and RealStatus is null '
end
--set @sql+=' ) as IDWithRowNumber where IDRank>'+ convert(nvarchar(10),@pageSizes )+'*'+convert(nvarchar(10),@pageIndex ) +' and IDRank<='+ convert(nvarchar(10),@pageSizes )+'*('+convert(nvarchar(10),@pageIndex ) +'+1)'
end
ELSE
begin
--set @sql+=' union all select '''',Seq_no as 序列号,Destination_no as 发送号码,Sms_SendContents as 短信内容,Send_Status as 提交状态,Send_Date as 发送时间,Oper_Date as 操作时间,Send_Predate as 预发时间, case is_Pre when 1 then ''任务发送'' else ''非任务发送'' end as 是否任务,RealStatus as 到达状态,MtDateTime as 状态时间,id,MessageType,isnull(MmcIndex,1) as MmcIndex from (select *,Row_number() over(order by Oper_Date desc) as IDRank from B_Sms_Send_info_'+@PipeId+' where Send_user in('+@user_code+') and (Oper_Date >
=convert(datetime,'''+@DateStart+' 00:00:00'',120) or ''''= '''+@DateStart+''' ) and (Oper_Date <=convert(datetime,'''+@DateEnd+' 23:59:59'',120) or ''''= '''+@DateEnd+''' ) and (Destination_no='''+@Destination_no+''' or ''''='''+@Destination_no+''')'
set @sql+=' union all select '''' as 序号,Seq_no as 序列号,Destination_no as 发送号码,Sms_SendContents as 短信内容,Send_Status as 提交状态,Send_Date as 发送时间,Oper_Date as 操作时间,id,MessageType,isnull(MmcIndex,1) as MmcIndex from B_Sms_Send_info_'+@PipeId+' where Send_user in('+@user_code+') and (Oper_Date >=convert(datetime,'''+@DateStart+' 00:00:00'',120) or ''''= '''+@DateStart+''' ) and (Oper_Date <=convert(datetime,'''+@DateEnd+' 23:59:59'',120) or ''''= '''+@DateEnd+''' ) and (Destination_no='''+@Destination_no+''' or ''''='''+@Destination_no+''') and Send_Status!=-11 '
set @sqlCount+=' union all select COUNT(*) as Counts from B_Sms_Send_Info_'+@PipeId+' where Send_user in('+@user_code+') and (Oper_Date >=convert(datetime,'''+@DateStart+' 00:00:00'',120) or ''''= '''+@DateStart+''' ) and (Oper_Date <=convert(datetime,'''+@DateEnd+' 23:59:59'',120) or ''''= '''+@DateEnd+''' ) and (Destination_no='''+@Destination_no+''' or ''''='''+@Destination_no+''') and Send_Status!=-11 '
if(@Status='0')
begin
set @sql+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) '
set @sqlCount+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) '
end
if(@Status='1')
begin
set @sql+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) and Send_Status=1 '
set @sqlCount+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) and Send_Status=1 '
end
if(@Status='-1')
begin
set @sql+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) and Send_Status=-1 '
set @sqlCount+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) and Send_Status=-1 '
end
if(@Status='DELIVRD')
begin
set @sql+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) and RealStatus = ''DELIVRD'' '
set @sqlCount+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) and RealStatus = ''DELIVRD'' '
end
if(@Status='2')
begin
set @sql+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) and (RealStatus <> ''DELIVRD'' and RealStatus <> '' and RealStatus is not null) '
set @sqlCount+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) and (RealStatus <> ''DELIVRD'' and RealStatus <> '' and RealStatus is not null) '
end
if(@Status='NULL')
begin
set @sql+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) and RealStatus is null '
set @sqlCount+=' and MessageType ='+@MessageType+' and (Sms_SendContents like ''%'+@Sms_SendContents+'%''or ''''='''+@Sms_SendContents+''' ) and RealStatus is null '
end
--set @sql+=' ) as IDWithRowNumber where IDRank>'+ convert(nvarchar(10),@pageSizes )+'*'+convert(nvarchar(10),@pageIndex ) +' and IDRank<='+ convert(nvarchar(10),@pageSizes )+'*('+convert(nvarchar(10),@pageIndex ) +'+1)'
end
end
END
SET @j = @j+1
END
print @sql
----创建视图
declare @dropsql nvarchar(max)
set @view_name='view_'+REPLACE(REPLACE(REPLACE(CONVERT(varchar(100),GETDATE(),20),'-','_'),' ','_'),':','_') --时间用横替换
--select REPLACE(REPLACE(REPLACE(CONVERT(varchar(100),GETDATE(),20),'-','_'),' ','_'),':','_')
---判断视图是否存在 存在就删除
set @dropsql='drop view ['+convert(varchar(100),@view_name)+']'
if exists(select * from sysobjects where name=@view_name)
exec(@dropsql)
--创建视图
set @dropsql='create view ['+convert(varchar(100),@view_name)+'] as '+@sql+''
EXEC(@dropsql)
exec(@sqlCount)
END