项目中的一些存储过程

来源：互联网发布：云建站系统源码编辑：程序博客网时间：2024/06/05 09:47

/*=======================================
对象名称：PR_USER_GETPERSONALCOMPLETE
功能描述：获取个人用户信息完成百分比
参数说明：
调用示例：EXECUTE PR_USER_GETPERSONALCOMPLETE 1, 1
作   者：Xu Yu
创建时间：-11-9
修改人：
修改时间：
========================================*/
CREATE PROCEDURE PR_USER_GETPERSONALCOMPLETE
(
    @USER_ID INT,
    @LANGUAGE INT
)
AS
    DECLARE @TOTAL DECIMAL(18,2);
    DECLARE @COMPLETE DECIMAL(18,2);
    DECLARE @TEMP INT;
    DECLARE @TEMP_STR NVARCHAR(MAX);
BEGIN
    SET @TOTAL = 18;
    SET @COMPLETE = 10;

    --工作经验
    SELECT @TEMP=COUNT(1) FROM WORK_EXPERIENCE WHERE [USER_ID]=@USER_ID AND LANGUAGE=@LANGUAGE;
    IF @TEMP > 0
       SET @COMPLETE = @COMPLETE + 1;

    --学习经历
    SELECT @TEMP=COUNT(1) FROM LEARNING_EXPERIENCE WHERE [USER_ID]=@USER_ID AND LANGUAGE=@LANGUAGE;
    IF @TEMP > 0
       SET @COMPLETE = @COMPLETE + 1;

    --论著
    SELECT @TEMP=COUNT(1) FROM USER_PUBLICATION WHERE [USER_ID]=@USER_ID AND LANGUAGE=@LANGUAGE;
    IF @TEMP > 0
       SET @COMPLETE = @COMPLETE + 1;

    --专业证书
    SELECT @TEMP=COUNT(1) FROM PROFESSIONAL_CERTIFICATE WHERE [USER_ID]=@USER_ID AND LANGUAGE=@LANGUAGE;
    IF @TEMP > 0
       SET @COMPLETE = @COMPLETE + 1;

    --专业协会
    SELECT @TEMP=COUNT(1) FROM PROFESSIONAL_ASSOCIATION WHERE [USER_ID]=@USER_ID AND LANGUAGE=@LANGUAGE;
    IF @TEMP > 0
       SET @COMPLETE = @COMPLETE + 1;

    --专业技能
    SELECT @TEMP=COUNT(1) FROM PROFESSIONAL_SKILL WHERE [USER_ID]=@USER_ID AND LANGUAGE=@LANGUAGE;
    IF @TEMP > 0
       SET @COMPLETE = @COMPLETE + 1;

    --知识产权
    SELECT @TEMP=COUNT(1) FROM INTELLECTUAL_PROPERTY WHERE [USER_ID]=@USER_ID AND LANGUAGE=@LANGUAGE;
    IF @TEMP > 0
       SET @COMPLETE = @COMPLETE + 1;

    --自我介绍
    SELECT @TEMP_STR=SELF_INTRODUCTION FROM [USER] WHERE [USER_ID]=@USER_ID;
    IF @TEMP_STR IS NOT NULL AND @TEMP_STR <> ''
       SET @COMPLETE = @COMPLETE + 1;

    SELECT CONVERT(INT, ROUND(@COMPLETE / @TOTAL * 100, 0));
END;
GO

0 0