ALTER PROCEDURE dwxxhz
(
@nd int
)
AS
/* SET NOCOUNT ON */
declare @dm nvarchar(10)
declare @dwmc nvarchar(50)
delete from dwhzb where 年度=@nd
declare mycr CURSOR FOR
select 代码,单位名称 from dwxx
OPEN mycr
FETCH NEXT FROM mycr into @dm,@dwmc
WHILE @@FETCH_STATUS=0
BEGIN
declare @bkysrs numeric
declare @zkysrs numeric
declare @flysrs numeric
declare @zrs numeric
declare @bkbl numeric(30,30)
declare @zkbl numeric(30,30)
declare @flbl numeric(30,30)
declare @zbkbl nvarchar(50)
declare @zzkbl nvarchar(50)
declare @zflbl nvarchar(50)
if @dm=700
begin
select @zrs=count(编号) from base where 年度=@nd
select @bkysrs=count(编号) from base where 年度=@nd and (现学历='博士' or 现学历='研究生' or 现学历='本科')
select @zkysrs=count(编号) from base where 年度=@nd AND (现学历='博士' or 现学历='研究生' or 现学历='本科' or 现学历='专科')
select @flysrs=count(编号) from base where 年度=@nd and (现学历专业分类 = '法律' OR 现学历专业分类 = '监所管理' OR 现学历专业分类 = '心理学' OR 现学历专业分类 = '教育学') AND ((现学历='博士' or 现学历='研究生' or 现学历='本科' or 现学历='专科'))
end
else
begin
select @zrs=count(编号) from base where 年度=@nd and 单位编号=@dm
select @bkysrs=count(编号) from base where 年度=@nd and 单位编号=@dm AND (现学历='博士' or 现学历='研究生' or 现学历='本科')
select @zkysrs=count(编号) from base where 年度=@nd and 单位编号=@dm AND (现学历='博士' or 现学历='研究生' or 现学历='本科' or 现学历='专科')
select @flysrs=count(编号) from base where 年度=@nd and 单位编号=@dm and (现学历专业分类 = '法律' OR 现学历专业分类 = '监所管理' OR 现学历专业分类 = '心理学' OR 现学历专业分类 = '教育学') AND (现学历='博士' or 现学历='研究生' or 现学历='本科' or 现学历='专科')
end
if (@zrs<>0)
begin
if ( @bkysrs<>0 and @zrs<>@bkysrs)
begin
set @bkbl=Convert(decimal(10,10),@bkysrs/@zrs)
set @zbkbl=substring(Convert(nvarchar(50),@bkbl*100),1,5)+'%'
end
else if (@zrs=@bkysrs)
begin
set @zbkbl='100%'
end
else
begin
set @zbkbl='0.00%'
end
if (@zkysrs<>0 and @zkysrs<>@zrs)
begin
set @zkbl=Convert(decimal(10,10),@zkysrs/@zrs)
set @zzkbl=substring(Convert(nvarchar(50),@zkbl*100),1,5)+'%'
end
else if(@zrs=@zkysrs)
begin
set @zzkbl='100%'
end
else
begin
set @zzkbl='0.00%'
end
if (@flysrs<>0 and @flysrs<>@zrs)
begin
set @flbl=Convert(decimal(10,10),@flysrs/@zrs)
set @zflbl=substring(Convert(nvarchar(50),@flbl*100),1,5)+'%'
end
else if (@flysrs=@zrs)
begin
set @flbl='100%'
end
else
begin
set @flbl='0.00%'
end
end
else
begin
set @zbkbl='0.00%'
set @zzkbl='0.00%'
set @zflbl='0.00%'
end
insert into dwhzb values(@dwmc,@zrs,@bkysrs,@zbkbl,@zkysrs,@zzkbl,@flysrs,@zflbl,@nd,@dm) FETCH NEXT FROM mycr into @dm,@dwmc
END
CLOSE mycr
DEALLOCATE mycr
RETURN运行报错提示 将数据类型 varchar 转换为 numeric 时出错。
(
@nd int
)
AS
/* SET NOCOUNT ON */
declare @dm nvarchar(10)
declare @dwmc nvarchar(50)
delete from dwhzb where 年度=@nd
declare mycr CURSOR FOR
select 代码,单位名称 from dwxx
OPEN mycr
FETCH NEXT FROM mycr into @dm,@dwmc
WHILE @@FETCH_STATUS=0
BEGIN
declare @bkysrs numeric
declare @zkysrs numeric
declare @flysrs numeric
declare @zrs numeric
declare @bkbl numeric(30,30)
declare @zkbl numeric(30,30)
declare @flbl numeric(30,30)
declare @zbkbl nvarchar(50)
declare @zzkbl nvarchar(50)
declare @zflbl nvarchar(50)
if @dm=700
begin
select @zrs=count(编号) from base where 年度=@nd
select @bkysrs=count(编号) from base where 年度=@nd and (现学历='博士' or 现学历='研究生' or 现学历='本科')
select @zkysrs=count(编号) from base where 年度=@nd AND (现学历='博士' or 现学历='研究生' or 现学历='本科' or 现学历='专科')
select @flysrs=count(编号) from base where 年度=@nd and (现学历专业分类 = '法律' OR 现学历专业分类 = '监所管理' OR 现学历专业分类 = '心理学' OR 现学历专业分类 = '教育学') AND ((现学历='博士' or 现学历='研究生' or 现学历='本科' or 现学历='专科'))
end
else
begin
select @zrs=count(编号) from base where 年度=@nd and 单位编号=@dm
select @bkysrs=count(编号) from base where 年度=@nd and 单位编号=@dm AND (现学历='博士' or 现学历='研究生' or 现学历='本科')
select @zkysrs=count(编号) from base where 年度=@nd and 单位编号=@dm AND (现学历='博士' or 现学历='研究生' or 现学历='本科' or 现学历='专科')
select @flysrs=count(编号) from base where 年度=@nd and 单位编号=@dm and (现学历专业分类 = '法律' OR 现学历专业分类 = '监所管理' OR 现学历专业分类 = '心理学' OR 现学历专业分类 = '教育学') AND (现学历='博士' or 现学历='研究生' or 现学历='本科' or 现学历='专科')
end
if (@zrs<>0)
begin
if ( @bkysrs<>0 and @zrs<>@bkysrs)
begin
set @bkbl=Convert(decimal(10,10),@bkysrs/@zrs)
set @zbkbl=substring(Convert(nvarchar(50),@bkbl*100),1,5)+'%'
end
else if (@zrs=@bkysrs)
begin
set @zbkbl='100%'
end
else
begin
set @zbkbl='0.00%'
end
if (@zkysrs<>0 and @zkysrs<>@zrs)
begin
set @zkbl=Convert(decimal(10,10),@zkysrs/@zrs)
set @zzkbl=substring(Convert(nvarchar(50),@zkbl*100),1,5)+'%'
end
else if(@zrs=@zkysrs)
begin
set @zzkbl='100%'
end
else
begin
set @zzkbl='0.00%'
end
if (@flysrs<>0 and @flysrs<>@zrs)
begin
set @flbl=Convert(decimal(10,10),@flysrs/@zrs)
set @zflbl=substring(Convert(nvarchar(50),@flbl*100),1,5)+'%'
end
else if (@flysrs=@zrs)
begin
set @flbl='100%'
end
else
begin
set @flbl='0.00%'
end
end
else
begin
set @zbkbl='0.00%'
set @zzkbl='0.00%'
set @zflbl='0.00%'
end
insert into dwhzb values(@dwmc,@zrs,@bkysrs,@zbkbl,@zkysrs,@zzkbl,@flysrs,@zflbl,@nd,@dm) FETCH NEXT FROM mycr into @dm,@dwmc
END
CLOSE mycr
DEALLOCATE mycr
RETURN运行报错提示 将数据类型 varchar 转换为 numeric 时出错。
还有,顺便说一句,你下面的赋值最好不要放在游标里面,你这样做不是在游标每一次操作的时候都会再执行一遍吗? declare @bkysrs numeric
declare @zkysrs numeric
declare @flysrs numeric
declare @zrs numeric
declare @bkbl numeric(30,30)
declare @zkbl numeric(30,30)
declare @flbl numeric(30,30)
declare @zbkbl nvarchar(50)
declare @zzkbl nvarchar(50)
declare @zflbl nvarchar(50)