sqlserver 创建月表视图过程

来源：互联网发布：c语言两个等于号编辑：程序博客网时间：2024/06/04 20:15
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);ENDSET @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; ENDGOexec hy_GenView 'GoodsSaleStock'
0 0