关于变量
YY_TEST.dbo.gh_ghzdk 因为这个是接口数据库里边的，名字有可能会改变，在存储过程里边用到6次
问题1：能否用一个变量代替，只需要在开始的时候赋值就行，该怎么写？
问题2：如果可以，那么这个变量也可以通过 存储过程参数 有程序提供对吗？
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[P_AppointPercent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[P_AppointPercent]GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO-- =============================================-- 爽约率查询-- =============================================CREATE PROCEDURE P_AppointPercent@S_BeginTime datetime , --开始时间 是由查询的医生提供@S_EndTime datetime , --结束时间@DeptID NVarChar(50), --部门编号@AllDept bit, --查询所有部门@CountApp int out, --预约人数@CountAppYse int out, --赴约人数@CountAppNo int out --赴约人数ASset @S_BeginTime=convert( varchar(100), @S_BeginTime, 23) --标准化时间格式set @S_EndTime=convert( varchar(100), @S_EndTime, 23) --标准化时间格式if @AllDept=1begin select @CountApp=count(*) --预约人数 from AppointList where AppointTime >= @S_BeginTime and AppointTime<=@S_EndTime and cancel=0 -- cancel=0 表示有效预约，=1表示该预约已经取消 select @CountAppYse=count(*) --赴约人数 from AppointList a left join YY_TEST.dbo.gh_ghzdk b on a.MedicalNo=b.blh and dbo.f_IsSameDate(a.AppointTime,b.ghrq)=1 where AppointTime >= @S_BeginTime and AppointTime<= @S_EndTime and b.blh is not null and a.cancel=0 select @CountAppNo=count(*) --爽约人数,算出爽约率就行了，在对赴约进行重复记录Distinc是，可能去掉预约多科室的情况。 from AppointList a left join YY_TEST.dbo.gh_ghzdk b on a.MedicalNo=b.blh and dbo.f_IsSameDate(a.AppointTime,b.ghrq)=1 where AppointTime >= @S_BeginTime and AppointTime<= @S_EndTime and b.blh is null and a.cancel=0 --a.* --爽约记录列表 --select periodName,dlTo.DeptName DeptName,dlFrom.DeptName FromDeptName,convert( varchar(100), AppointTime, 23)AppointTime, select periodName,'所有科室' DeptName,dlFrom.DeptName FromDeptName,convert( varchar(100), AppointTime, 23)AppointTime, dbo.f_realName(a.ManCreate) ManCreate, a.* from AppointList a left join YY_TEST.dbo.gh_ghzdk b on a.MedicalNo=b.blh and dbo.f_IsSameDate(a.AppointTime,b.ghrq)=1 left join periodList per on a.periodID=per.periodID left join DeptList dlTo on a.DeptID=dlTo.ID left join DeptList dlFrom on a.FromDeptID=dlFrom.ID where AppointTime >= @S_BeginTime and AppointTime<= @S_EndTime and b.blh is null and a.cancel=0 order by AppointTime,MedicalNoendelsebegin select @CountApp=count(*) --预约人数 from AppointList where AppointTime >= @S_BeginTime and AppointTime<=@S_EndTime and cancel=0 and DeptID=@DeptID -- cancel=0 表示有效预约，=1表示该预约已经取消 select @CountAppYse=count(*) --赴约人数 from AppointList a left join gh_ghzdk b on a.MedicalNo=b.blh and dbo.f_IsSameDate(a.AppointTime,b.ghrq)=1 where AppointTime >= @S_BeginTime and AppointTime<= @S_EndTime and b.blh is not null and a.cancel=0 and a.DeptID=@DeptID select @CountAppNo=count(*) --爽约人数,算出爽约率就行了，在对赴约进行重复记录Distinc是，可能去掉预约多科室的情况。 from AppointList a left join gh_ghzdk b on a.MedicalNo=b.blh and dbo.f_IsSameDate(a.AppointTime,b.ghrq)=1 where AppointTime >= @S_BeginTime and AppointTime<= @S_EndTime and b.blh is null and a.cancel=0 and a.DeptID=@DeptID --a.* --爽约记录列表 select periodName,dlTo.DeptName DeptName,dlFrom.DeptName FromDeptName,convert( varchar(100), AppointTime, 23)AppointTime, dbo.f_realName(a.ManCreate) ManCreate, a.* from AppointList a left join gh_ghzdk b on a.MedicalNo=b.blh and dbo.f_IsSameDate(a.AppointTime,b.ghrq)=1 left join periodList per on a.periodID=per.periodID left join DeptList dlTo on a.DeptID=dlTo.ID left join DeptList dlFrom on a.FromDeptID=dlFrom.ID where AppointTime >= @S_BeginTime and AppointTime<= @S_EndTime and b.blh is null and a.cancel=0 and a.DeptID=@DeptID order by AppointTime,MedicalNoendGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
