Sql 存储过程即返回结果集又返回值以及动态创建视图

太过爱你忘了你带给我的痛 2022-01-10 11:53 184阅读 0赞

/****** Object:  StoredProcedure [dbo].[GetXiaFa_Record_Tow]    Script Date: 07/10/2013 23:50:33 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[GetXiaFa_Record_Tow]
    	    @PipeIDs  nvarchar(1000) ,--基础通道ID
    	    @DateStart  nvarchar(30),
    		@DateEnd nvarchar(30),
    		@cnt int,				--基础通道ID个数
    		@Status		nvarchar(10),		--发送状态
    		@Destination_no varchar(1000),  --接收号码
    		@Sms_SendContents varchar(max),  --短信内容
    		@user_code varchar(50),          --用户
    		@MessageType nvarchar(10),  --是短信 还是彩信
    		@IsStatus nvarchar(10),     --用户类型 是管理员还是普通用户  普通用户没有回执状态
    		@pageIndex int , --当前页
    		@pageSizes  int, -- 一个页面显示多少条数据
    		@view_name nvarchar(100) output  --返回视图名
    AS
    BEGIN
    	 DECLARE @j INT=1,@PipeId nvarchar(100)  --- 分割上传的列
    DECLARE @sql nvarchar(max)=''
    declare @sqlCount nvarchar(max)=''
    WHILE @j<=@cnt 
    	BEGIN
    		SET @PipeId= dbo.EDM_FN_SplitString(@PipeIDs,',',@j)
    		IF LEN(@PipeId)>0
    		BEGIN
    			if(@IsStatus='1') --是否让这个用户显示短信发送的真实状态
    			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 B_Sms_Send_info_'+@PipeId+' where Send_user='''+@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 操作时间, RealStatus as 到达状态,MtDateTime 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 B_Sms_Send_info_'+@PipeId+' where Send_user='''+@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 操作时间,RealStatus as 到达状态,MtDateTime 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
    			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

C\#程序调用上面存储过程 获取结果集和值：

string View_Name = "";
                            try
                            {
                                // 在此处放置用户代码以初始化页面
                                String DBConnStr;
                                DataSet MyDataSet = new DataSet();
                                System.Data.SqlClient.SqlDataAdapter DataAdapter = new System.Data.SqlClient.SqlDataAdapter();
                                string serverIP = ConfigurationManager.AppSettings["ServerIP"];
                                string serverUser = ConfigurationManager.AppSettings["ServerUser"];
                                string dbName = ConfigurationManager.AppSettings["DbName"];
                                string userPwd = ConfigurationManager.AppSettings["UserPwd"];
                                DBConnStr = "server=" + serverIP + ";database=" + dbName + ";pwd=" + userPwd + ";uid=" + serverUser + "";
                                System.Data.SqlClient.SqlConnection myConnection = new System.Data.SqlClient.SqlConnection(DBConnStr);
                                if (myConnection.State != ConnectionState.Open)
                                {
                                    myConnection.Open();
                                }
                                System.Data.SqlClient.SqlCommand myCommand = new System.Data.SqlClient.SqlCommand("GetXiaFa_Record_Tow", myConnection);
                                myCommand.CommandType = CommandType.StoredProcedure;
                                //添加输入查询参数、赋予值
                                myCommand.Parameters.Add("@PipeIDs", SqlDbType.VarChar);
                                myCommand.Parameters["@PipeIDs"].Value = pips;
                                myCommand.Parameters.Add("@DateStart", SqlDbType.VarChar);
                                myCommand.Parameters["@DateStart"].Value = sbutract;
                                myCommand.Parameters.Add("@DateEnd", SqlDbType.VarChar);
                                myCommand.Parameters["@DateEnd"].Value = currentDate;
                                myCommand.Parameters.Add("@cnt", SqlDbType.VarChar);
                                myCommand.Parameters["@cnt"].Value = cnt;
                                myCommand.Parameters.Add("@Status", SqlDbType.VarChar);
                                myCommand.Parameters["@Status"].Value = SendStatus;
                                myCommand.Parameters.Add("@Destination_no", SqlDbType.VarChar);
                                myCommand.Parameters["@Destination_no"].Value = this.txtDesno.Text;
                                myCommand.Parameters.Add("@Sms_SendContents", SqlDbType.VarChar);
                                myCommand.Parameters["@Sms_SendContents"].Value = this.txtSmsContents.Text;
                                myCommand.Parameters.Add("@user_code", SqlDbType.VarChar);
                                myCommand.Parameters["@user_code"].Value = selectUser;
                                myCommand.Parameters.Add("@MessageType", SqlDbType.VarChar);
                                myCommand.Parameters["@MessageType"].Value = "1";
                                myCommand.Parameters.Add("@IsStatus", SqlDbType.VarChar);
                                myCommand.Parameters["@IsStatus"].Value = userInfo.IsStatus;
                                myCommand.Parameters.Add("@pageIndex", SqlDbType.VarChar);
                                myCommand.Parameters["@pageIndex"].Value = this.PageInfo.CurrentPageIndex - 1;
                                myCommand.Parameters.Add("@pageSizes", SqlDbType.VarChar);
                                myCommand.Parameters["@pageSizes"].Value = this.PageInfo.PageSize;
                                //添加输出参数
                                myCommand.Parameters.Add("@view_name", SqlDbType.VarChar, 50);
                                myCommand.Parameters["@view_name"].Direction = ParameterDirection.Output;
    
    
                                myCommand.ExecuteNonQuery();
                                DataAdapter.SelectCommand = myCommand;
    
                                DataAdapter.Fill(MyDataSet, "table");
    
                                //获取数据条数
                                if (MyDataSet != null && MyDataSet.Tables.Count > 0)
                                {
                                    for (int i = 0; i < MyDataSet.Tables[0].Rows.Count; i++)
                                    {
                                        PageCount += int.Parse(MyDataSet.Tables[0].Rows[i]["Counts"].ToString());
                                    }
                                }
                                MyDataSet.Dispose();
                                //得到存储过程输出参数
                                View_Name = myCommand.Parameters["@view_name"].Value.ToString();
                                if (myConnection.State == ConnectionState.Open)
                                {
                                    myConnection.Close();
                                }
                            }
                            catch (Exception ex)
                            {
                                Base.WriteErrorLog("下发记录查询3天内——创建视图异常：" + ex.Message.Replace(Environment.NewLine, string.Empty), HttpContext.Current.Server.MapPath("ErrorLog\\"));
                            }
                            if (View_Name != "")
                            {
                                Dictionary<string, object> dicTo = new Dictionary<string, object>();
                                dicTo.Add("viewName", View_Name);
                                dicTo.Add("pageIndex", int.Parse(this.PageInfo.CurrentPageIndex.ToString()) - 1);
                                dicTo.Add("pageSizes", this.PageInfo.PageSize);
                                DataSet ds = Base.GetDataSet("dbo.GetXiaFa_Record_View", dicTo);
                                if (ds != null && ds.Tables.Count > 0)
                                {
                                    dt2 = ds.Tables[0];
                                }
                            }