日期:2014-05-17 浏览次数:20558 次
CREATE PROCEDURE P_AppointPercent_test2
@S_BeginTime datetime , --开始时间 是由查询的医生提供
@S_EndTime datetime , --结束时间
@DeptID NVarChar(50), --部门编号
@AllDept bit, --查询所有部门
@CountApp int out, --预约人数
@CountAppYse int out, --赴约人数
@CountAppNo int out --赴约人数
AS
set @sql=N' select @CountApp=count(*) '+
N' from AppointList a ' +
N' left join ' + @TableName +' b on a.MedicalNo=b.blh and dbo.f_IsSameDate(a.AppointTime,b.ghrq)=1 ' +
N' where AppointTime >= @S_BeginTime and AppointTime<= @S_EndTime and b.blh is null and a.cancel=0 '
exec sp_executesql @sql,N'@S_BeginTime datetime,@S_EndTime datetime,@CountApp int output',@S_BeginTime,@S_EndTime,@CountApp output
declare @TableName nvarchar(100),@Sql nvarchar(1000)
declare @S_BeginTime datetime,@S_EndTime datetime
declare @CountApp int --output
set @TableName = 'YY_TEST.dbo.gh_ghzdk b'
select @S_BeginTime='2012-04-18',@S_EndTime='2012-04-19'
set @sql=N'select @CountApp=count(*) '+
N'from AppointList a ' +
N'left join ' + @TableName +' on a.MedicalNo=b.blh and dbo.f_IsSameDate(a.AppointTime,b.ghrq)=1' +
N'where AppointTime >= @S_BeginTime and AppointTime<= @S_EndTime and b.blh is null and a.cancel=0'
exec sp_executesql @sql,N'@S_BeginTime datetime,@S_EndTime datetime,@CountApp int output',@S_BeginTime,@S_EndTime,@CountApp output
select @CountApp
你的这 @CountAppYse int out, --赴约人数
@CountAppNo int out --赴约人数
两 没有接收赋值的过程
也的放到 exec sp_executesql @sql,N'@S_BeginTime datetime,@S_EndTime datetime,@CountApp int output',@S_BeginTime,@S_EndTime,@CountApp output
这个里做相应的声明 和接收过程