Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2011
    Posts
    2

    Smile Unanswered: need help in creating procedure

    Dear friends !
    Can anyone help me with part : Retrieve password for existing account written in stored procedure manner
    Discription :Candidate asks to return password.

    Input : Candidate’s email address

    Process: Check existence of email in the database If email exists in the DB,
    send new pass If not, inform “This email does not exist!”

    Output: Inform returning new password for the existing account successfully
    or not

    Data storage : New password.
    Thanks in advance

    Bests

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Please ask your PM or manager for a better explanation, I'm not quite sure what they're asking you to do.

    -PatP
    Quote Originally Posted by phoebe90 View Post
    Dear friends !
    Can anyone help me with part : Retrieve password for existing account written in stored procedure manner
    Discription :Candidate asks to return password.

    Input : Candidate’s email address

    Process: Check existence of email in the database If email exists in the DB,
    send new pass If not, inform “This email does not exist!”

    Output: Inform returning new password for the existing account successfully
    or not

    Data storage : New password.
    Thanks in advance

    Bests
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2011
    Posts
    2
    Hi
    Well, it is my homework that my teacher gave me. They ask us to building the database and running the queries statement to test all use cases. And every use cases’ query statement must be written in stored procedure manner. After every creating stored procudure statement, i should have executing statement for all exceptions cases for each use case
    For example: proc_CheckEmail 'email', you should write some cases to test
    eg: case 1: email that lacks of @
    case 2: email that contains special keyword
    case 3: email that lacks of domain .com .vn ...
    something like that
    Or proc_register
    Test for case of duplicating username or email or case of username that is not valid...
    Below is my code(but it has not yet finished and contains many errors that i am trying to work out) and also, a attached file about this small project. However, please have a look at these to better understand what i have been doing
    HTML Code:
    SET NOCOUNT ON
    go
    create database human_resource_management;
    go
    use human_resource_management;
    go
    
    create table CV(
    	cvID int identity(1,1)NOT NULL,
    	candidateID int NULL,
    	can_img image NULL,
    	working_area nvarchar(60) NULL,
    	working_place nvarchar(60) NULL,
    	position nvarchar(60) NULL,
    	workingTime nvarchar(60) NULL,
    	salary nvarchar(60) NULL,
    	degree nvarchar(60) NULL,
    	chung_chi nvarchar(60) NULL,
    	university nvarchar(60) NULL,
    	uni_address nvarchar(60)NULL,
    	falcuty nvarchar(60) NULL,
    	graduated nvarchar(60) NULL,
    	used_job nvarchar(60) NULL,
    	num_experience nvarchar(60) NULL,
    	description nvarchar(60)NULL,
    	submitTime nvarchar(60)NULL,
    	ngon_ngu nvarchar(60)NULL,
    	CONSTRAINT "FK_CV_candidate" FOREIGN KEY 
    	(
    		"candidateID"
    	) REFERENCES "dbo"."candidate" (
    		"candidateID"
    	)
    );
    go
    insert into CV values(1, 'A.jpg', 'Hapro','HK', 'CAO','8h','$3000', 'university', 'good',
    'foreign trade','HN', 'Business administration', '2002','CAO','5 years',  'expert', '8.00', 'E');
    go
    insert into CV values(2,'A.jpg', 'Hapro','HN', 'CAO','8h','$3000', 'university', 
    'foreign trade','HN', 'Business administration', '2002','CAO','5 years',  'expert', '8.00', 'E');
    go
    insert into CV values(1,'A.jpg', 'Hapro','HN', 'CAO','8h','$3000', 'university', 
    'foreign trade','HN', 'Business administration', '2002','CAO','5 years',  'expert', '8.00', 'E');
    go
    
    create table candidate(
    	"candidateID" "int" not null identity(1,1)  ,
    	fullname nvarchar(60) null,
    	sex nvarchar(60) null,
    	DOB nvarchar(60) null,
    	marriage_stt nvarchar(60) null,
    	address nvarchar(60) null,
    	distrist nvarchar(60) null,
    	city nvarchar(60) null,
    	phone int(60) null,
    	email nvarchar(60) null,
    	username nvarchar(60) null,
    	password nvarchar(60) null,
    	candidateStatus nvarchar(60) null,
    	blocked nvarchar(60) null,
    	time_chstt nvarchar(60) null
    	 CONSTRAINT "PK_candidateID" PRIMARY KEY  CLUSTERED 
    	(
    		"candidateID"
    	)
    
    );
    go
    create table employer(
    	employerID int,
    	em_img image,
    	fullname nvarchar,
    	sex nvarchar,
    	username nvarchar,
    	password nvarchar,
    	companyName nvarchar,
    	companyEmail nvarchar,
    	companyAddress nvarchar,
    	company_fax nvarchar,
    	companyPhone nvarchar,
    	companyField nvarchar,
    	company_Description nvarchar,
    	website nvarchar,
    	timeRegister nvarchar,
    	lastVisit nvarchar,
    	blocked nvarchar
    	 
    
    );
    go
    create table quantri(
    	ID int,
    	fullname nvarchar,
    	username nvarchar,
    	password nvarchar,
    	bac nvarchar
    	 
    
    );
    go
    create table FavouriteCandidate(
    	employerID int,
    	candidateID int,
    	addedTime nvarchar
    	 
    
    );
    go
    create table news(
    	newsID int,
    	newsTitle nvarchar,
    	newsContent nvarchar,
    	author nvarchar,
    	timepost nvarchar,
    	summary nvarchar
    	 
    
    );
    go
    drop procedure [dbo].[employer_login]
    create proc [dbo].[employer_login](
    	
    	@username nvarchar(50),
    	@password nvarchar(50),
    	@exist int output
    
    )
    as
    begin 
    if((select count (*) from employer where username = @username and password = @password) = 1)
    begin 
    	set @exist = 1
    PRINT 'log in successfully '
    end
    else set @exist = 0
    PRINT 'log in not successfully '
    end 
    --execute
    declare @output int
    exec employer_login 'employer', 'employer', @output output
    
    
    --2
    
    create proc [dbo].[employer_register](
    	@name nvarchar(50),
    	@username nvarchar(50),
    	@password nvarchar(50),
    	@email nvarchar(50),
    	@phone nvarchar(50),
    	@companyName nvarchar(50),
    	@companyEmail nvarchar(50),
    	@companyAddress nvarchar(50),
    	@company_fax nvarchar(50),
    	@companyPhone nvarchar(50),
    	@companyField nvarchar(50),
    	@company_Description nvarchar(50)
    )
    as
    begin 
    if((select count (*) from employer where username = @username)=1)
    		begin 
    			PRINT 'This account was used! Please choose another one!'
    		END
    	else 
    	set @exist = 0
    		BEGIN
    			PRINT 'You can use this account!'
    			--- kiem tra email hop. le.
    				
    			DECLARE @invalChars varchar(5),@valid int,@badChar varchar(1),@atPos
    int,@periodPos int
    SET @valid = 1
    SET @invalChars = ' /:,;'
    --Check to see if it's blank
    IF len(ltrim(rtrim(@email))) = 0
       SET @valid = 0
    ELSE
            --Loop invalid characters to see if it exists in email
          WHILE len(@invalChars) > 0
             BEGIN
                SET @badChar = substring(@invalChars,1,1)
                IF(charindex(@badChar,@email) > 0)
                   --If invalid character was found, return 0 to invalidate
                   SET @valid = 0
                SET @invalChars = replace(@invalChars,@badChar,'')
             END
          --Check to see if "@" exists.
          SET @atPos = charindex('@',@email,1)
          IF @atPos = 0
             SET @valid = 0
           --Check to see if extra "@" exists after 1st "@".
          IF charindex('@',@email,@atPos+1) > 0
             SET @valid = 0
          SET @periodPos = charindex('.',@email,@atPos)
          IF @periodPos = 0
             SET @valid = 0
          IF (@periodPos+3) > len(@email)
             SET @valid = 0
          RETURN (@valid)
    END
    
    --execute
    exec employer_register 'employer', 'employer','employer','employer' ,'employer' ,
    'employer' ,'employer', 'employer' ,'employer' ,'employer' ,'employer' ,'employer'
    
    --3
    create proc [dbo].[Candidate_login](
    	
    	@username nvarchar(50),
    	@password nvarchar(50),
    	@exist int output
    
    )
    as
    begin 
    if((select count (*) from Candidate where username = @username and password = @password) = 1)
    begin 
    	set @exist = 1
    PRINT 'log in successfully '
    end
    else set @exist = 0
    PRINT 'log in not successfully '
    end 
    --execute
    declare @output int
    exec Candidate_login 'Candidate', 'Candidate', @output output
    
    
    --4
    create proc [dbo].[Candidate_register](
    	@name nvarchar(50),
    	@username nvarchar(50),
    	@password nvarchar(50),
    	@email nvarchar(50),
    	@phone nvarchar(50),
    	@companyName nvarchar(50),
    	@companyEmail nvarchar(50),
    	@companyAddress nvarchar(50),
    	@company_fax nvarchar(50),
    	@companyPhone nvarchar(50),
    	@companyField nvarchar(50),
    	@company_Description nvarchar(50),
    	@exist int output
    
    )
    as
    begin 
    DECLARE @invalChars varchar(5),@valid int,@badChar varchar(1),@atPos
    int,@periodPos int
    SET @valid = 1
    SET @invalChars = ' /:,;'
    --Check to see if it's blank
    IF len(ltrim(rtrim(@email))) = 0
       SET @valid = 0
    ELSE
            --Loop invalid characters to see if it exists in email
          WHILE len(@invalChars) > 0
             BEGIN
                SET @badChar = substring(@invalChars,1,1)
                IF(charindex(@badChar,@email) > 0)
                   --If invalid character was found, return 0 to invalidate
                   SET @valid = 0
                SET @invalChars = replace(@invalChars,@badChar,'')
             END
          --Check to see if "@" exists.
          SET @atPos = charindex('@',@email,1)
          IF @atPos = 0
             SET @valid = 0
           --Check to see if extra "@" exists after 1st "@".
          IF charindex('@',@email,@atPos+1) > 0
             SET @valid = 0
          SET @periodPos = charindex('.',@email,@atPos)
          IF @periodPos = 0
             SET @valid = 0
          IF (@periodPos+3) > len(@email)
             SET @valid = 0
          RETURN (@valid)
    END
    
    
    --5
    create proc [dbo].[Admin_login](
    	
    	@username nvarchar(50),
    	@password nvarchar(50),
    	@exist int output
    
    )
    as
    begin 
    if((select count (*) from Admin where username = @username and password = @password) = 1)
    begin 
    	set @exist = 1
    PRINT 'log in successfully '
    end
    else set @exist = 0
    PRINT 'log in not successfully '
    end 
    --execute
    declare @output int
    exec Admin_login 'Candidate', 'Candidate', @output output
    Attached Files Attached Files

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Does your teacher know you're on a dbforum asking people to do your homework for you?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •