sql server2000行转列存储过程\\生成唯一性流水号

alter Proc dbo.Perfor_Score
@DateFrom  nvarchar(50) ,
@DateTo  nvarchar(50)
AS
declare @sqlText nvarchar(4000)
begin
if exists (select * from nscb.dbo.sysobjects where id = object_id(N'nscb..#dailybulletin_ManPowerSum') and type='U')
drop table #dailybulletin_ManPowerSum
select workshop,station_type,sum(real_manpowerQty) realQty
into #dailybulletin_ManPowerSum
from dailybulletin_ManPowerAssign where input_date between @DateFrom and @DateTo
group by workshop,station_type

set @sqlText='select workshop'
select  @sqlText=@sqlText+', sum(case station_type when'''+station_type+''' Then realQty else 0 end) as '''+station_type +''''  from (select distinct station_type from #dailybulletin_ManPowerSum) sub
set @sqlText=@sqlText+' from  #dailybulletin_ManPowerSum  group by workshop'
exec (@sqlText)
end

Exec dailybulletin_ManPowerRate '2012-12-9','2012-12-30'

 //第二个,包括全局临时表

ALTER     Proc dbo.Perfor_Score
@DateFrom  nvarchar(50),
@DateTo  nvarchar(50),
@DutyType  nvarchar(50),
@WorkShop nvarchar(50),
@Shift nvarchar(50),
@GH nvarchar(50),
@XM nvarchar(50)
AS
declare @sqlText nvarchar(4000)
declare @sqlSel nvarchar(4000)
declare @sqlWhere nvarchar(4000)
declare @sqlWhere1 nvarchar(4000)
declare @sqlWhere2 nvarchar(4000)
declare @ScoreSumText nvarchar(4000)
begin
if object_id('tempdb..##Perfor_ScoreSum') is not null
drop table ##Perfor_ScoreSum
if exists (select * from nscb.dbo.sysobjects where id = object_id(N'nscb..#Perfor_DetailScoreSum'))
drop table #Perfor_DetailScoreSum
set @sqlWhere='where (input_date between '''+@DateFrom+ ''' and '''+ @DateTo+''') '
if ((@DutyType is not null) and (isnull(@DutyType,'')<>''))
begin
set @sqlWhere=@sqlWhere+' and duty_type='''+@DutyType+''''
end
if ((@WorkShop is not null) and (isnull(@WorkShop,'')<>''))
begin
set @sqlWhere=@sqlWhere+' and workshop='''+@WorkShop+''''
end
if ((@Shift is not null) and (isnull(@Shift,'')<>''))
begin
set @sqlWhere=@sqlWhere+' and shift='''+@Shift+''''
end
if ((@GH is not null) and (isnull(@GH,'')<>''))
begin
set @sqlWhere=@sqlWhere+' and gh='''+@GH+''''
end
if ((@XM is not null) and (isnull(@XM,'')<>''))
begin
set @sqlWhere=@sqlWhere+' and xm='''+@XM+''''
end
set @ScoreSumText= 'select gh,''PerformanceTotalScore'' as per_type,sum(score) scoreSum  into ##Perfor_ScoreSum  from  performance_itemScore  '

--第一次创建##Perfor_ScoreSum 表时,要尽量插入较长的字符,它将规定次表字段的初始长度
exec (@ScoreSumText+@sqlWhere+' group by gh')
set @sqlWhere1=@sqlWhere+' group by  gh,per_type '
set @ScoreSumText='select gh,per_type,sum(score) scoreSum  from performance_itemScore '
exec('insert into ##Perfor_ScoreSum '+@ScoreSumText+@sqlWhere1)
set @sqlWhere2=@sqlWhere+' group by  gh,per_item '
set @ScoreSumText='select gh,per_item,sum(score) scoreSum  from performance_itemScore '
exec('insert into ##Perfor_ScoreSum '+ @ScoreSumText+@sqlWhere2)
set @sqlText='select gh '
select  @sqlText=@sqlText+', sum(case per_type when '''+per_type+''' Then scoreSum else 0 end) as '''+per_type +''''  from (select distinct per_type from ##Perfor_ScoreSum) sub
set @sqlText=@sqlText+' into #Perfor_DetailScoreSum  from  ##Perfor_ScoreSum  group by gh'
set @sqlSel= ' select distinct '''+@DateFrom+'''+''至''+'''+@DateTo+''' as 日期,p.workshop 工位,p.shift 班次,p.gh 工号,p.xm 姓名,p.duty 职务,p.duty_type 职务分类,p.direct_leader 直接管理人,d.* '
set @sqlSel= @sqlSel+' from #Perfor_DetailScoreSum d,performance_itemScore p where d.gh=p.gh'
exec (@sqlText+@sqlSel)
end

-------------生成并发唯一性流水号的解决方案

CREATE TABLE [dbo].[SerialNo](
    [sCode] [varchar](50) NOT NULL,--主键也是多个流水号的类别区分
    [sName] [varchar](100) NULL,--名称，备注形式
    [sQZ] [varchar](50) NULL,--前缀
    [sValue] [varchar](80) NULL,--因子字段
 CONSTRAINT [PK_SerialNo] PRIMARY KEY CLUSTERED
(
    [sCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
 ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 Create procedure [dbo].[GetSerialNo]  

(

    @sCode varchar(50)  

)    

  as  

begin  

   Declare @sValue  varchar(16),    

           @dToday   datetime,

           @sQZ  varchar(50)  --这个代表前缀  

   Begin Tran      

   Begin Try    

     -- 锁定该条记录，好多人用lock去锁，起始这里只要执行一句update就可以了

    --在同一个事物中，执行了update语句之后就会启动锁

     Update SerialNo set sValue=sValue where sCode=@sCode  

    Select @sValue = sValue From SerialNo where sCode=@sCode  

    Select @sQZ = sQZ From SerialNo where sCode=@sCode  

    -- 因子表中没有记录，插入初始值  

    If @sValue is null  

    Begin

      Select @sValue = convert(bigint, convert(varchar(6), getdate(), 12) + '000001')  

      Update SerialNo set sValue=@sValue where sCode=@sCode  

    end else  

    Begin               --因子表中没有记录  

      Select @dToday = substring(@sValue,1,6)  

      --如果日期相等，则加1  

      If @dToday = convert(varchar(6), getdate(), 12)  

        Select @sValue = convert(varchar(16), (convert(bigint, @sValue) + 1))  

      else              --如果日期不相等，则先赋值日期，流水号从1开始  

        Select @sValue = convert(bigint, convert(varchar(6), getdate(), 12) +'000001')  

       Update SerialNo set sValue =@sValue where sCode=@sCode    

     End  

     Select result = @sQZ+@sValue      

     Commit Tran    

   End Try    

   Begin Catch    

     Rollback Tran  

     Select result = 'Error'  

   End Catch    

end