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

最新推荐文章于 2022-05-22 11:01:43 发布
最新推荐文章于 2022-05-22 11:01:43 发布
阅读量4.3k 收藏 1
点赞数 1
分类专栏： C#、类库、方法 sql server 文章标签： sql 存储过程
本文为博主原创文章，未经博主允许不得转载。
本文链接：https://blog.csdn.net/chinaplan/article/details/9296437
版权 
/****** 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];
                            }
                        }





下一秒_待续
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
专栏目录
存储过程的安全及性能优化
03-07
解析阶段：指某个存储过程首次执行时，查询处理器从sys.sql_modules目录视图中读取该存储过程的文本并且检查该过程引用的对象是否存在的过程。 编译阶段：指分析存储过程和生成存储过程执行计划的过程。执行计划是...
将存储过程返回结果转为视图（Methods For Converting a Stored Procedure Result to a View）
GRANDTREE的专栏
11-03 1573
 http://www.sqlservercentral.com/articles/T-SQL/68233/    Create     proceduresp_ConvProc2View   ( @procName varchar(80), @viewName varchar(80)) 
把动态SQL结果集转化为视图查询的方法（运行环境：SQL2005以上版本）
金 色 的 草 棚
07-20 351
<br /><br />----------------------------------------------------------------------------------<br />-- Author : htl258(Tony)<br />-- Date   : 2010-06-10 01:49:18<br />-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) <br />--         
SQL Server 2008编程入门经典(第3版)
08-29
12.1 创建存储过程：基本语法 12.2 使用ALTER修改存储过程 12.3 删除存储过程 12.4 参数化 12.5 通过返回值确认成功或失败 12.6 错误处理 12.6.1 以前的方式 12.6.2 在错误发生前处理错误 12.6.3 手动引发错误 12.6....
SQL.Server.2008编程入门经典(第3版).part2.rar
06-09
《SQL Server 2008编程入门经典(第3版)》由浅入深逐步介绍了SQL Server数据库的高级主题，重点讨论了SQL Server 2008的特殊功能以及与其他编程环境的不同之处。作者Robert Vieria是Microsoft SQL，Server方面的权威...
SQL：存储过程中创建动态视图
Just For Learning
05-22 1014
需求：新建一存储过程 proc5，实现查询 购买过指定书目ID的用户 同时 该用户购买过的购买次数最多的前3名书目名称及购买次数 解决过程： 创建存储过程，为指定ID创建变量 CREATE PROCEDURE proc5 @book_id int 通过@book_id查询购买过指定书目ID的用户userId select distinct(userID) from orderBook ,orderInfo where orderBook.orderID=orderInfo.orderID
关于用存储过程中动态创建视图的一点心得。
VaJoy童鞋的专栏
01-07 6435
工作中遇到的问题，要不然也不会想到要去如何实现了。本来已经创建好了视图，后台的代码也写完了，突然得到指示，其中一张比较重要的表的表名会因为在不同的PC上安装数据库而发生变化（在另一张表中可以根据规则查询到对应的正确的表名），所以，想个办法解决吧。         第一感觉，郁闷！然后，是思考策略，反正大的前提是不想改代码，所以，只能从后台下手了，最好的解决办法就是利用存储过程动态创建视图，这样就
在存储过程中创建视图
gyflyx的专栏
03-21 1618
<br />发信人: Sage (魅力四射), 信区: Database<br />标  题: [转载] 存储过程中创建视图                     fangyu (转寄)<br />发信站: BBS 荔园晨风站 (Mon Sep  4 13:19:56 2000), 站内信件<br /><br />【 以下文字转载自 Sage 的信箱 】<br />【 原文由 [email protected] 所发表 】<br /><br />其实只是为了逻辑上清楚一些，<br />后来仔细想了想，
SQL Server 存储过程返回结果集的几种方式
cxu123321的博客
09-05 8847
SQL Server 返回结果集的几种方式 2017年12月18日 21:52:24xxc1605629895阅读数 7033更多 分类专栏：sqlserver 版权声明：本文为博主原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。 本文链接：https://blog.csdn.net/xxc1605629895/article/details/78...
如果存储过程返回多个结果集，怎么取结果
热门推荐
keenweiwei的专栏
01-08 1万+
//方式一：使用SqlDataAdapter SqlDataAdapter sda = new SqlDataAdapter(); DataSet ds = new DataSet(); sda.Fill(ds);//Fill DataSet:如果结果集有多个，对应dataset中的多个table
当存储过程返回有多个结果集时，默认返回第一个 需加上SET NOCOUNT ON
qq_29495141的博客
09-26 1383
问题表现： 创建一个存储过程，不加SET NOCOUNT ON时，会返回3个结果集 1、insert的结果集 2、select的结果集 3、select的结果集 当然用工具我们可以看到两个结果集，但是当代码里调用执行 EXEC [dbo].demo_SET_NOCOUNT_ON '' 默认是返回第一个结果集，也就是insert的结果集，所以代码返回会是一个空的结果集。 解决办法： 方法一、 在存储过程加上SET NOCOUNT ON; 这时返回的...
存储过程动态创建试图
jackother的专栏
12-06 668
CREATE OR REPLACE PROCEDURE CREATE_VIEW_P /********************************************************************************   中文名称：根据数据字典动态创建视图   功能描述：根据指定的数据库用户、表名信息从数据字典中提取字段名及字段描述，            
SQL server 查询返回多个结果集
weixin_43942765的博客
10-25 1149
今天做界面优化，要把企业总数量查询改成按照‘高级，普通，试用’三种类型分别计算其数量，对于这种查询方法如下 select (select Count(*) from EnterpriseInfo as a where a.EnterpriseType=0) as Gjnum, (select Count(*) from EnterpriseInfo as b where b.EnterpriseType=1) as Ptnum, (select Count(*) EnterpriseInfo as c w
oracle获取游标返回值,oracle函数返回结果集(动态查询Sql并定义游标)
最新发布
05-30
要获取游标返回值或者使用函数返回结果集，需要使用Oracle中的游标和PL/SQL语句。以下是一个例子： ``` CREATE OR REPLACE FUNCTION get_emp_salary (dept_id NUMBER) RETURN SYS_REFCURSOR IS emp_cursor SYS_REF...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

下一秒_待续

如有帮到您，给个赞赏(^.^)

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值