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
GO
SET 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=1
begin
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,MedicalNoend
else
begin
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,MedicalNo
endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
可以,但需要用动态SQL来执行
Create PROCEDURE GetDataList
(
@TableName nvarchar(200),--数据表名称
)
....
....
--获取总记录数
IF @SelectWhere !='' --如果没有设置查询条件
SET @TemSelect = N'SELECT @TemCount = count(*) FROM '+@TableName+ ' WHERE '+@SelectWhere
ELSE
SET @TemSelect = N'SELECT @TemCount = count(*) FROM '+@TableName
--检查字段值是否存在,字段类型为int;如果存在 1,否则0(@Exist 输出参数)
CREATE PROCEDURE ExistDataInt
@TableName Nvarchar (50) ,--数据库表名
@FieldName NVarChar (50),--字段名
@Value int,--字段值
@Exist bit output--输出参数,如果存在 1,否则0
AS
declare @sql nvarchar(1000)
set @sql=N' select * from '+@TableName +
N' where '+@FieldName+
N' = @Value'+
N' set @Exist=(case when @@rowcount>0 then 1 else 0 end)'
exec sp_executesql @sql,N'@Value int,@Exist bit output',@Value,@Exist output
GO问题:
1. N' = @Value' 变量在单引号里边,N' where '+@FieldName+ 变量为什么没有在单引号里边?
2. exec sp_executesql @sql,N'@Value int,@Exist bit output',@Value,@Exist output
后边这一段,N'@Value int,@Exist bit output',@Value,@Exist output 怎么解释?