declare @levels int,@i int,@j int
declare @year int,@month1 int,@month2 int
declare @sql nvarchar(4000)
------------临时参数
select @year=2009
select @month1=1
select @month2=12
----------------------
if exists (select * from sysobjects where id = object_id(N'[dbo].[Test_01]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Test_01
create table Test_01
(
Num bigint IDENTITY(1,1) not null,
RSMid bigint,
RSMName nvarchar(50),--RSM姓名
DSMid bigint,
DSMName nvarchar(50),--DSM姓名
MRid bigint,
MRName nvarchar(50),--MR姓名
mr_post_id bigint
)
set @levels=(select count(level_code) from base_doctorlevel)
set @i=0
while (@i<=@levels)
begin
if @i<10
begin
set @sql=@sql+' alter table Test_01 add m'+(replicate ('0', 2)+convert(nvarchar,@i))+' decimal(10,4) null default null '
set @i=@i+1
execute sp_executesql @sql
set @sql=''
end
if @i>=10
begin
set @sql=@sql+' alter table Test_01 add m'+(replicate ('0', 1)+convert(nvarchar,@i))+' decimal(10,4) null default null '
set @i=@i+1
execute sp_executesql @sql
set @sql=''
end
endinsert into Test_01 (RSMid,RSMName,DSMid,DSMName,MRid,MRName,mr_post_id)
select rsm_id,rsm_name,dsm_id,dsm_name,mr_id,mr_name,mr_post_id from middle_region
where
mr_id in (select staff_id from man_staff where post_id in (select post_id from reg_post where direct_parent=2))
group by rsm_id,rsm_name,dsm_id,dsm_name,mr_id,mr_name,mr_post_id--目标医生个数
if exists (select * from sysobjects where id = object_id(N'[dbo].[Test_02]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Test_02
declare @levels2 int,@i2 int
declare @sql2 nvarchar(4000)
create table Test_02
(
Num bigint,
mr_post_id bigint
)
set @levels2=(select count(level_code) from base_doctorlevel)
set @i2=0
while (@i2<=@levels2)
begin
if @i2<10
begin
set @sql2=@sql2+' alter table Test_02 add m'+(replicate ('0', 2)+convert(nvarchar,@i2))+' decimal(10,4) null default null '
set @i2=@i2+1
execute sp_executesql @sql2
set @sql2=''
end
if @i2>=10
begin
set @sql2=@sql2+' alter table Test_02 add m'+(replicate ('0', 1)+convert(nvarchar,@i2))+' decimal(10,4) null default null '
set @i2=@i2+1
execute sp_executesql @sql2
set @sql2=''
end
end--岗位对应医生级别医生数 临时表
if exists (select * from sysobjects where id = object_id(N'[dbo].[Test_03]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Test_03
create table Test_03
(
doctorNum bigint,
level_code nvarchar(50),
post_id bigint
)insert into Test_03
select count(doctor_id) as doctorNum ,level_code,post_id from reg_targetdoctor
where post_id in(select mr_post_id from Test_01)
group by level_code,post_idinsert into Test_02 (mr_post_id,m001,m002,m003,m004,m005,m006,m007,m008,m009,m010,m011,m012,m013,m014,m015,m016,m017)
select post_id,
m001=sum(case level_code when '001' then doctornum end),
m002=sum(case level_code when '002' then doctornum end),
m003=sum(case level_code when '003' then doctornum end),
m004=sum(case level_code when '004' then doctornum end),
m005=sum(case level_code when '005' then doctornum end),
m006=sum(case level_code when '006' then doctornum end),
m007=sum(case level_code when '007' then doctornum end),
m008=sum(case level_code when '008' then doctornum end),
m009=sum(case level_code when '009' then doctornum end),
m010=sum(case level_code when '010' then doctornum end),
m011=sum(case level_code when '011' then doctornum end),
m012=sum(case level_code when '012' then doctornum end),
m013=sum(case level_code when '013' then doctornum end),
m014=sum(case level_code when '014' then doctornum end),
m015=sum(case level_code when '015' then doctornum end),
m016=sum(case level_code when '016' then doctornum end),
m017=sum(case level_code when '017' then doctornum end)from Test_03
group by post_id
order by post_id--岗位对应医生级别拜访医生数 临时表
if exists (select * from sysobjects where id = object_id(N'[dbo].[Test_04]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Test_04
create table Test_04
(
doctorNum bigint,
level_code nvarchar(50),
post_id bigint
)insert into test_04
select count(distinct b.doctor_id) as doctorNum ,b.level_code,b.post_id from (
SELECT dbo.work_doctorcall.doctor_id, dbo.reg_targetdoctor.level_code, dbo.work_doctorcall.work_date, dbo.man_staff.post_id
FROM dbo.work_doctorcall INNER JOIN
dbo.reg_targetdoctor ON dbo.work_doctorcall.doctor_id = dbo.reg_targetdoctor.doctor_id INNER JOIN
dbo.man_staff ON dbo.work_doctorcall.user_id = dbo.man_staff.staff_id) b
where b.post_id in(select mr_post_id from Test_01) and year(work_date) = @year and month(work_date) between @month1 and @month2
group by level_code,post_id
declare @levels5 int,@i5 int
declare @sql5 nvarchar(4000)
if exists (select * from sysobjects where id = object_id(N'[dbo].[Test_05]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Test_05
create table Test_05
(
Num bigint,
mr_post_id bigint
)
set @levels5=(select count(level_code) from base_doctorlevel)
set @i5=0
while (@i5<=@levels5)
begin
if @i5<10
begin
set @sql5=@sql5+' alter table Test_05 add m'+(replicate ('0', 2)+convert(nvarchar,@i5))+' decimal(10,4) null default null '
set @i5=@i5+1
execute sp_executesql @sql5
set @sql5=''
end
if @i5>=10
begin
set @sql5=@sql5+' alter table Test_05 add m'+(replicate ('0', 1)+convert(nvarchar,@i5))+' decimal(10,4) null default null '
set @i5=@i5+1
execute sp_executesql @sql5
set @sql5=''
end
endinsert into Test_05 (mr_post_id,m001,m002,m003,m004,m005,m006,m007,m008,m009,m010,m011,m012,m013,m014,m015,m016,m017)
select post_id,
m001=isnull(sum(case level_code when '001' then doctornum end),0),
m002=isnull(sum(case level_code when '002' then doctornum end),0),
m003=isnull(sum(case level_code when '003' then doctornum end),0),
m004=isnull(sum(case level_code when '004' then doctornum end),0),
m005=isnull(sum(case level_code when '005' then doctornum end),0),
m006=isnull(sum(case level_code when '006' then doctornum end),0),
m007=isnull(sum(case level_code when '007' then doctornum end),0),
m008=isnull(sum(case level_code when '008' then doctornum end),0),
m009=isnull(sum(case level_code when '009' then doctornum end),0),
m010=isnull(sum(case level_code when '010' then doctornum end),0),
m011=isnull(sum(case level_code when '011' then doctornum end),0),
m012=isnull(sum(case level_code when '012' then doctornum end),0),
m013=isnull(sum(case level_code when '013' then doctornum end),0),
m014=isnull(sum(case level_code when '014' then doctornum end),0),
m015=isnull(sum(case level_code when '015' then doctornum end),0),
m016=isnull(sum(case level_code when '016' then doctornum end),0),
m017=isnull(sum(case level_code when '017' then doctornum end),0)
from Test_04
group by post_id
order by post_id
update Test_02
set num=(select c.num from (
SELECT dbo.Test_01.Num, dbo.Test_02.mr_post_id
FROM dbo.Test_01 INNER JOIN
dbo.Test_02 ON dbo.Test_01.mr_post_id = dbo.Test_02.mr_post_id) c
where test_02.mr_post_id=c.mr_post_id)
update Test_05
set num=(select c.num from(
SELECT dbo.Test_01.Num, dbo.Test_05.mr_post_id
FROM dbo.Test_01 INNER JOIN
dbo.Test_05 ON dbo.Test_01.mr_post_id = dbo.Test_05.mr_post_id) c
where test_05.mr_post_id=c.mr_post_id)
declare @actul decimal(10,4)declare @target decimal(10,4)
declare @sqlactul nvarchar(4000)
declare @sqltarget nvarchar(4000)set @i=1while @i<=(select count(num) from test_01)
begin
set @j=1
while @j<=17
begin
begin
------------------------------------就是下面if @j<10这段循环----------------------
if @j<10
begin
set @sql='update test_01 set m'+(replicate ('0', 2)+convert(nvarchar,@j))+'=(select m'+(replicate ('0', 2)+convert(nvarchar,@j))+' from test_05 where num='
+(cast((@i) as char(4)))+')/(select m'+(replicate ('0', 2)+convert(nvarchar,@j))+' from test_02 where num='+(cast((@i) as char(4)))+')
where test_01.num='+(cast((@i) as char(4)))+''
execute sp_executesql @sql
set @sql=''
end
------------------------------------到此为止-------------------------------------
if @j>=10
begin
set @sql='update test_01 set m'+(replicate ('0', 1)+convert(nvarchar,@j))+'=(select m'+(replicate ('0', 1)+convert(nvarchar,@j))+' from test_05 where num='
+(cast((@i) as char(4)))+')/(select m'+(replicate ('0', 1)+convert(nvarchar,@j))+' from test_02 where num='+(cast((@i) as char(4)))+')
where test_01.num='+(cast((@i) as char(4)))+''
execute sp_executesql @sql
set @sql=''
end
end
set @j=@j+1
end
set @i=@i+1
endselect * from test_01
------------------------------------------以上部分我单独执行时可以得到我想要查询的结果,没有任何问题
------------------------------------------当我把它放进一个存储过程里后被标明的那段循环就没有被执行
declare @year int,@month1 int,@month2 int
declare @sql nvarchar(4000)
------------临时参数
select @year=2009
select @month1=1
select @month2=12
----------------------
if exists (select * from sysobjects where id = object_id(N'[dbo].[Test_01]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Test_01
create table Test_01
(
Num bigint IDENTITY(1,1) not null,
RSMid bigint,
RSMName nvarchar(50),--RSM姓名
DSMid bigint,
DSMName nvarchar(50),--DSM姓名
MRid bigint,
MRName nvarchar(50),--MR姓名
mr_post_id bigint
)
set @levels=(select count(level_code) from base_doctorlevel)
set @i=0
while (@i<=@levels)
begin
if @i<10
begin
set @sql=@sql+' alter table Test_01 add m'+(replicate ('0', 2)+convert(nvarchar,@i))+' decimal(10,4) null default null '
set @i=@i+1
execute sp_executesql @sql
set @sql=''
end
if @i>=10
begin
set @sql=@sql+' alter table Test_01 add m'+(replicate ('0', 1)+convert(nvarchar,@i))+' decimal(10,4) null default null '
set @i=@i+1
execute sp_executesql @sql
set @sql=''
end
endinsert into Test_01 (RSMid,RSMName,DSMid,DSMName,MRid,MRName,mr_post_id)
select rsm_id,rsm_name,dsm_id,dsm_name,mr_id,mr_name,mr_post_id from middle_region
where
mr_id in (select staff_id from man_staff where post_id in (select post_id from reg_post where direct_parent=2))
group by rsm_id,rsm_name,dsm_id,dsm_name,mr_id,mr_name,mr_post_id--目标医生个数
if exists (select * from sysobjects where id = object_id(N'[dbo].[Test_02]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Test_02
declare @levels2 int,@i2 int
declare @sql2 nvarchar(4000)
create table Test_02
(
Num bigint,
mr_post_id bigint
)
set @levels2=(select count(level_code) from base_doctorlevel)
set @i2=0
while (@i2<=@levels2)
begin
if @i2<10
begin
set @sql2=@sql2+' alter table Test_02 add m'+(replicate ('0', 2)+convert(nvarchar,@i2))+' decimal(10,4) null default null '
set @i2=@i2+1
execute sp_executesql @sql2
set @sql2=''
end
if @i2>=10
begin
set @sql2=@sql2+' alter table Test_02 add m'+(replicate ('0', 1)+convert(nvarchar,@i2))+' decimal(10,4) null default null '
set @i2=@i2+1
execute sp_executesql @sql2
set @sql2=''
end
end--岗位对应医生级别医生数 临时表
if exists (select * from sysobjects where id = object_id(N'[dbo].[Test_03]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Test_03
create table Test_03
(
doctorNum bigint,
level_code nvarchar(50),
post_id bigint
)insert into Test_03
select count(doctor_id) as doctorNum ,level_code,post_id from reg_targetdoctor
where post_id in(select mr_post_id from Test_01)
group by level_code,post_idinsert into Test_02 (mr_post_id,m001,m002,m003,m004,m005,m006,m007,m008,m009,m010,m011,m012,m013,m014,m015,m016,m017)
select post_id,
m001=sum(case level_code when '001' then doctornum end),
m002=sum(case level_code when '002' then doctornum end),
m003=sum(case level_code when '003' then doctornum end),
m004=sum(case level_code when '004' then doctornum end),
m005=sum(case level_code when '005' then doctornum end),
m006=sum(case level_code when '006' then doctornum end),
m007=sum(case level_code when '007' then doctornum end),
m008=sum(case level_code when '008' then doctornum end),
m009=sum(case level_code when '009' then doctornum end),
m010=sum(case level_code when '010' then doctornum end),
m011=sum(case level_code when '011' then doctornum end),
m012=sum(case level_code when '012' then doctornum end),
m013=sum(case level_code when '013' then doctornum end),
m014=sum(case level_code when '014' then doctornum end),
m015=sum(case level_code when '015' then doctornum end),
m016=sum(case level_code when '016' then doctornum end),
m017=sum(case level_code when '017' then doctornum end)from Test_03
group by post_id
order by post_id--岗位对应医生级别拜访医生数 临时表
if exists (select * from sysobjects where id = object_id(N'[dbo].[Test_04]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Test_04
create table Test_04
(
doctorNum bigint,
level_code nvarchar(50),
post_id bigint
)insert into test_04
select count(distinct b.doctor_id) as doctorNum ,b.level_code,b.post_id from (
SELECT dbo.work_doctorcall.doctor_id, dbo.reg_targetdoctor.level_code, dbo.work_doctorcall.work_date, dbo.man_staff.post_id
FROM dbo.work_doctorcall INNER JOIN
dbo.reg_targetdoctor ON dbo.work_doctorcall.doctor_id = dbo.reg_targetdoctor.doctor_id INNER JOIN
dbo.man_staff ON dbo.work_doctorcall.user_id = dbo.man_staff.staff_id) b
where b.post_id in(select mr_post_id from Test_01) and year(work_date) = @year and month(work_date) between @month1 and @month2
group by level_code,post_id
declare @levels5 int,@i5 int
declare @sql5 nvarchar(4000)
if exists (select * from sysobjects where id = object_id(N'[dbo].[Test_05]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Test_05
create table Test_05
(
Num bigint,
mr_post_id bigint
)
set @levels5=(select count(level_code) from base_doctorlevel)
set @i5=0
while (@i5<=@levels5)
begin
if @i5<10
begin
set @sql5=@sql5+' alter table Test_05 add m'+(replicate ('0', 2)+convert(nvarchar,@i5))+' decimal(10,4) null default null '
set @i5=@i5+1
execute sp_executesql @sql5
set @sql5=''
end
if @i5>=10
begin
set @sql5=@sql5+' alter table Test_05 add m'+(replicate ('0', 1)+convert(nvarchar,@i5))+' decimal(10,4) null default null '
set @i5=@i5+1
execute sp_executesql @sql5
set @sql5=''
end
endinsert into Test_05 (mr_post_id,m001,m002,m003,m004,m005,m006,m007,m008,m009,m010,m011,m012,m013,m014,m015,m016,m017)
select post_id,
m001=isnull(sum(case level_code when '001' then doctornum end),0),
m002=isnull(sum(case level_code when '002' then doctornum end),0),
m003=isnull(sum(case level_code when '003' then doctornum end),0),
m004=isnull(sum(case level_code when '004' then doctornum end),0),
m005=isnull(sum(case level_code when '005' then doctornum end),0),
m006=isnull(sum(case level_code when '006' then doctornum end),0),
m007=isnull(sum(case level_code when '007' then doctornum end),0),
m008=isnull(sum(case level_code when '008' then doctornum end),0),
m009=isnull(sum(case level_code when '009' then doctornum end),0),
m010=isnull(sum(case level_code when '010' then doctornum end),0),
m011=isnull(sum(case level_code when '011' then doctornum end),0),
m012=isnull(sum(case level_code when '012' then doctornum end),0),
m013=isnull(sum(case level_code when '013' then doctornum end),0),
m014=isnull(sum(case level_code when '014' then doctornum end),0),
m015=isnull(sum(case level_code when '015' then doctornum end),0),
m016=isnull(sum(case level_code when '016' then doctornum end),0),
m017=isnull(sum(case level_code when '017' then doctornum end),0)
from Test_04
group by post_id
order by post_id
update Test_02
set num=(select c.num from (
SELECT dbo.Test_01.Num, dbo.Test_02.mr_post_id
FROM dbo.Test_01 INNER JOIN
dbo.Test_02 ON dbo.Test_01.mr_post_id = dbo.Test_02.mr_post_id) c
where test_02.mr_post_id=c.mr_post_id)
update Test_05
set num=(select c.num from(
SELECT dbo.Test_01.Num, dbo.Test_05.mr_post_id
FROM dbo.Test_01 INNER JOIN
dbo.Test_05 ON dbo.Test_01.mr_post_id = dbo.Test_05.mr_post_id) c
where test_05.mr_post_id=c.mr_post_id)
declare @actul decimal(10,4)declare @target decimal(10,4)
declare @sqlactul nvarchar(4000)
declare @sqltarget nvarchar(4000)set @i=1while @i<=(select count(num) from test_01)
begin
set @j=1
while @j<=17
begin
begin
------------------------------------就是下面if @j<10这段循环----------------------
if @j<10
begin
set @sql='update test_01 set m'+(replicate ('0', 2)+convert(nvarchar,@j))+'=(select m'+(replicate ('0', 2)+convert(nvarchar,@j))+' from test_05 where num='
+(cast((@i) as char(4)))+')/(select m'+(replicate ('0', 2)+convert(nvarchar,@j))+' from test_02 where num='+(cast((@i) as char(4)))+')
where test_01.num='+(cast((@i) as char(4)))+''
execute sp_executesql @sql
set @sql=''
end
------------------------------------到此为止-------------------------------------
if @j>=10
begin
set @sql='update test_01 set m'+(replicate ('0', 1)+convert(nvarchar,@j))+'=(select m'+(replicate ('0', 1)+convert(nvarchar,@j))+' from test_05 where num='
+(cast((@i) as char(4)))+')/(select m'+(replicate ('0', 1)+convert(nvarchar,@j))+' from test_02 where num='+(cast((@i) as char(4)))+')
where test_01.num='+(cast((@i) as char(4)))+''
execute sp_executesql @sql
set @sql=''
end
end
set @j=@j+1
end
set @i=@i+1
endselect * from test_01
------------------------------------------以上部分我单独执行时可以得到我想要查询的结果,没有任何问题
------------------------------------------当我把它放进一个存储过程里后被标明的那段循环就没有被执行
--存储过程--
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[proc_report_call_coverage_rate]
-- Add the parameters for the stored procedure here
@staff_id bigint,
@m1 int,
@m2 int,
@year intasdeclare @title_code nvarchar(100) ,@N int,@idsm int,@irsm int,@postid int
declare @sql nvarchar(4000)declare @sqlnull nvarchar(4000)
declare @sql0 nvarchar(4000)
declare @levels int,@i int,@j int
declare @month1 int,@month2 int
if exists (select * from sysobjects where id = object_id(N'[dbo].[middle_report_call_coverage_rate]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table middle_report_call_coverage_rate
create table middle_report_call_coverage_rate---这个表结构跟前面的test_01是一样的
(
Num bigint IDENTITY(1,1) not null,
nmonth int,
RSMid bigint,
RSMName nvarchar(30),--RSM姓名
DSMid bigint,
DSMName nvarchar(30),--DSM姓名
MRid bigint,
MRName nvarchar(30),--MR姓名
mr_post_id bigint
)------------临时参数
--select @year=2009
--select @month1=1
--select @month2=12
----------------------set @sql=''
set @sqlnull=''
set @sql0=''
declare @mycolumn nvarchar(10)declare doctorlevel cursor
for select distinct level_code from Sandoz.dbo.base_doctorlevelopen doctorlevel
fetch next from doctorlevel into @mycolumn
while @@fetch_status=0
beginset @sql=@sql+' alter table middle_report_call_coverage_rate add m'+@mycolumn+' nvarchar(10) null default null '
fetch next from doctorlevel into @mycolumnend
close doctorlevel
DEALLOCATE doctorlevelexecute sp_executesql @sql
set @sql=''
begin try
select @title_code = staff_title_code from man_staff where staff_id = @staff_id
if @staff_id = 0
begin
--很多很多
goto finish
end
if dbo.IsMr(@title_code)=1
begin
--很多很多
goto finish
end
if dbo.IsDsm(@title_code) = 1
begin
--这里就是一楼贴出的部分只有部分表名变量名不同,但表结构与其完全相同
--在这里第一段循环未能被执行
goto finish
end
finish: select * from middle_report_call_coverage_rateend try
begin catch DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INt SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE() RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
)
end catch就是这样