存储过程中创建视图

create proc sp_GenerateSalesNumber
as
 declare   @sqlstr   varchar(255)  
  if   exists   (select   *   from   sysobjects   where   id   =   object_id(N'v_temp')   and   OBJECTPROPERTY(id,   N'IsView')   =   1)  
    begin  
    select   @sqlstr   =   'drop   view   v_temp'  
    exec(@sqlstr)  
    end  
   
  begin  
    select   @sqlstr   =   'create   view   v_temp   as   select ROW_NUMBER() over(order by productID) as rowid,* from MG_Product'
    exec(@sqlstr)  
  end
 declare @rowcount int
 select @rowcount =MAX(rowid) from v_temp
 while(@rowcount>0)
 begin
  update v_temp set SalesNumber=cast( floor(rand()*100) as int) where rowid=@rowcount
  set @rowcount=@rowcount-1
  print @rowcount
 end
 drop view v_temp
 go
exec sp_GenerateSalesNumber

转载于:https://www.cnblogs.com/helpwz/articles/2490202.html