USE 医到家健康服务系统;
--创建存储过程；
----查询患者计数；
GO
CREATE PROCEDURE usp_selectPatientCount
	(@P_No VARCHAR(6)
	,@P_Password VARCHAR(20))
	AS
BEGIN
	SELECT 
			COUNT(1)
		FROM
			tb_Patient AS P
		WHERE
			P.P_No=@P_No
			AND P.P_Password=HASHBYTES('MD5',@P_Password);
END
----插入患者；
GO
CREATE PROCEDURE usp_insertPatient
	(@P_No VARCHAR(6)
	,@P_Password VARCHAR(20))
	AS
BEGIN
	INSERT tb_Patient
		(P_No,P_Password)
		VALUES
		(@P_No
		,HASHBYTES('MD5',@P_Password));
END

----更新患者密码；
GO
CREATE PROCEDURE usp_updatePatientPassword
	(@P_No VARCHAR(6)
	,@P_NewPassword VARCHAR(20))
	AS
BEGIN
	UPDATE tb_Patient
		SET
			P_Password=HASHBYTES('MD5',@P_NewPassword)
		WHERE
			P_No=@P_No;
END

--创建存储过程；
----查询医生计数；
GO
CREATE PROCEDURE usp_selectDoctorCount
	(@D_No VARCHAR(6)
	,@D_Password VARCHAR(20))
	AS
BEGIN
	SELECT 
			COUNT(1)
		FROM
			tb_Doctor AS D
		WHERE
			D.D_No=@D_No
			AND D.D_Password=HASHBYTES('MD5',@D_Password);
END
----插入医生；
GO
CREATE PROCEDURE usp_insertDoctor
	(@D_No CHAR(6)
	,@D_Name VARCHAR(10)
	,@D_Sex Char(2)
	,@D_Password VARCHAR(20)
	,@DoctorNo Char(6)
	,@D_Tel Char(11))
	AS
BEGIN
	INSERT tb_Doctor
		(D_No,D_Name,D_Sex,D_Password,DoctorNo,D_Tel)
		VALUES
		(@D_No
		,@D_Name
		,@D_Sex
		,HASHBYTES('MD5',@D_Password)
		,@DoctorNo
		,@D_Tel);
END
----更新医生密码；
GO
CREATE PROCEDURE usp_updateDoctorPassword
	(@D_No VARCHAR(6)
	,@D_NewPassword VARCHAR(20))
	AS
BEGIN
	UPDATE tb_Doctor
		SET
			D_Password=HASHBYTES('MD5',@D_NewPassword)
		WHERE
			D_No=@D_No;
END



--创建存储过程；
----插入问题；
GO
CREATE PROCEDURE usp_insertAsk
	(@Ask_No CHAR(10)
	,@Asker VARCHAR(10)
	,@Ask VARCHAR(MAX))
	AS
BEGIN
	INSERT tb_Ask
		(Ask_No,Asker,Ask)
		VALUES
		(@Ask_No
		,@Asker
		,@Ask);
END