sqlserver 创建月表视图过程

最新推荐文章于 2023-10-10 15:59:24 发布

mysk8868

最新推荐文章于 2023-10-10 15:59:24 发布

阅读量761

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/mysk8868/article/details/48655841

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

sqlserver 创建月表视图过程，方便数据查询。

CREATE Procedure [dbo].[HY_GenView] @tablename varchar(32) -- 需调用的表名 
AS BEGIN 
  declare @Err int; 
  declare @BreakPoint  int; 
  declare @Msg varchar(255); 
  declare @strTableName char(32),@SQL varchar(8000),@SQLString varchar(8000),@ViewName varchar(32);
  declare @StartDT smalldatetime,@EndDT smalldatetime;
  select @StartDT='2014-01-01',@EndDT=getdate()
  if @EndDT<@StartDT
  begin
  select @Msg='请检查日期：开始日期大于结束日期!',@BreakPoint=359550; 
  goto ErrHandle; 
  end 
  -- 获取该视图的各列名 
  declare @i int,@id int,@max int,@str varchar(2000)
  select @id=id from sysobjects where name=@tablename and xtype='u'
  IF @id IS NULL
  BEGIN
  select @Msg='请检查数据表!',@BreakPoint=359551;
  goto ErrHandle; 
  END
  select @max=max(colorder),@i=1 from syscolumns where id=@id 
  select @str=name from syscolumns where id=@id and colorder=@i 
  while @i<@max begin 
	 select @i=@i+1 
	 select @str=@str+','+name from syscolumns where id=@id and colorder=@i 
  END
  -- 取视图名，并判断该视图是否存在，存在则删除
  select @ViewName='HY_'+@tablename+'View' 
  select @SQLString='if object_id('''+@ViewName+''',''V'') is not null drop view '+@ViewName+';'
  print(@SQLString);exec (@SQLString);
-- 获取创建视图的语句 
	select @SQL='Create View '+@ViewName+' as ' 
	select @tablename=ltrim(rtrim(@tablename)) 
	select @strTableName=@tablename+convert(varchar(6),@StartDT,112)  
	SET @SQLString=''
	WHILE convert(varchar(6),@StartDT,112)<=convert(varchar(6),@EndDT,112)
	BEGIN  
	  SELECT @strTableName=@tablename+convert(varchar(6),@StartDT,112) 
	  IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE name=@strTableName AND xtype='u' )
		SELECT @SQLString=ltrim(rtrim(@SQLString))+' union all select '+@str+' from '+@strTableName
		  ELSE
		 IF year(@StartDT)=year(@EndDT) AND month(@StartDT)=month(@EndDT)
			  SELECT @SQLString=ltrim(rtrim(@SQLString))+' union all select '+@str+' from '+@tablename
	  SELECT @StartDT=dateadd(MONTH,1,@StartDT);
	END
	SET @SQLString=@SQL+substring(@SQLString,11,len(@SQLString))
-- 执行创建视图的语句 
  PRINT @SQLString;exec (@SQLString);
-- 报错处理 
  select @Err=@@Error,@Msg='取表名时出错！',@BreakPoint=359551; 
  if (@Err is null) or (@Err!=0)  goto ErrHandle; 
  return 0;
  ErrHandle: 
  raiserror('%s,断点=%d,Err=%d',16,1,@Msg,@BreakPoint,@Err); 
  return -1; 
END
GO
exec hy_GenView 'GoodsSaleStock'