SQL存储过程+视图

---存储过程举例

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'***'))

    DROP PROCEDURE ***
 GO
create procedure ***(@tablename as nvarchar(256),@ret int output)          
as          
 declare @sSql as  nvarchar(4000)         
        
 set @sSql = N'select top 1 * from ' + @tablename       
 exec(@sSql)        
 if @@rowcount = 0        
 begin        
   set @ret=2        
   return        
 end    
   
 select convert(int,0) as id,convert(money,0) as ufts,convert(money,0) as corufts into #arr where 1=0  
 set @sSql=N'insert into #arr select arr.**,convert(money,arr.crm_timestamp) as ufts ,arrs.corufts           
   from  (select a.***,t.ufts as corufts from           
    '+@tablename+' t left join *** a with (updlock) on a.***=t.autoid group by a.***,t.ufts ) arrs          
    left join *** arr with (updlock)           
    on arr.***=arrs.*** '  
 exec(@sSql)  
 if exists(select top 1 id  from #arr where (ufts<>corufts or id is null) and  corufts is not null )          
 begin           
  set @ret=1  --         
  return          
 end  
   
   
 set @sSql=N'select top 1 a.partapply_d_id from            
    '+@tablename+' t inner join *** a with (updlock) on a.***=t.***   
    where (isnull(t.***,0) + isnull(a.***,0)>isnull(a.***,0)) '          
 exec(@sSql)     
 if @@rowcount > 0        
 begin        
   set @ret=3 --数据校验          
   return        
 end  
   
 update *** set ***=*** from *** inner join #arr on ***.*** = #arr.id       
   
 set @sSql=N'update a set a.***=isnull(a.***,0)+isnull(t.***,0), a.***=isnull(a.***,0)+isnull(t.***,0)   
 from *** a join '+@tablename+' t on a.***=t.autoid'   
 exec(@sSql)  
 set @ret=0          
 
 return   

go

---视图举例

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[***'))
 DROP ***
GO
CREATE view   ***  as
select pa.*** as ***, rd.ID as ID
from *** rds WITH(NOLOCK)
inner join *** pad on rds.***=pad.***
inner join *** pa on rds.***=pa.***
inner join *** rd on rd.***=N'***'
go