SQL Server Performance

need help about procedure

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Phoebe, Jan 4, 2011.

  1. Phoebe New Member

    <p>&nbsp;<font color="midnightblue" face="Verdana, Arial, Helvetica" size="2"><span class="spnMessageText" id="msg">Dear friends !<br>I have done a small project and i had some difficulties that i need your help to fix it. My task in this project is building the database and running the queries statement to test all use cases: customers/ candidate/ admin. And every use cases’ query statement must be written in stored procedure manner. <br>Below is my code with many error,please help me check and fix it. Thanks in advance <img src="http://www.sqlteam.com/forums/images/icon_smile.gif" mce_src="http://www.sqlteam.com/forums/images/icon_smile.gif" alt="" title="" width="15" align="middle" border="0" height="15"></span></font></p><p>&nbsp;</p><p><font color="midnightblue" face="Verdana, Arial, Helvetica" size="2"><span class="spnMessageText" id="msg"><pre id="code"><font id="code" face="courier" size="2">SET NOCOUNT ON<br>go<br>create database human_resource_management;<br>use human_resource_management;<br>create table CV(<br>cvID int,<br>candidateID int,<br>can_img image,<br>working_area nvarchar,<br>working_place nvarchar,<br>position nvarchar,<br>workingTime nvarchar,<br>salary nvarchar,<br>degree nvarchar,<br>chung_chi nvarchar,<br>university nvarchar,<br>uni_address nvarchar,<br>falcuty nvarchar,<br>graduated nvarchar,<br>used_job nvarchar,<br>num_experience nvarchar,<br>description nvarchar,<br>submitTime nvarchar,<br>ngon_ngu nvarchar<br> <br><br>);<br>create table candidate(<br>candidateID int,<br>fullname nvarchar,<br>sex nvarchar,<br>DOB nvarchar,<br>marriage_stt nvarchar,<br>address nvarchar,<br>distrist nvarchar,<br>city nvarchar,<br>phone int,<br>email nvarchar,<br>username nvarchar,<br>password nvarchar,<br>candidateStatus nvarchar,<br>blocked nvarchar,<br>time_chstt nvarchar<br> <br><br>);<br>create table employer(<br>employerID int,<br>em_img image,<br>fullname nvarchar,<br>sex nvarchar,<br>username nvarchar,<br>password nvarchar,<br>companyName nvarchar,<br>companyEmail nvarchar,<br>companyAddress nvarchar,<br>company_fax nvarchar,<br>companyPhone nvarchar,<br>companyField nvarchar,<br>company_Description nvarchar,<br>website nvarchar,<br>timeRegister nvarchar,<br>lastVisit nvarchar,<br>blocked nvarchar<br> <br><br>);<br>create table quantri(<br>ID int,<br>fullname nvarchar,<br>username nvarchar,<br>password nvarchar,<br>bac nvarchar<br> <br><br>);<br>create table FavouriteCandidate(<br>employerID int,<br>candidateID int,<br>addedTime nvarchar<br> <br><br>);<br>create table news(<br>newsID int,<br>newsTitle nvarchar,<br>newsContent nvarchar,<br>author nvarchar,<br>timepost nvarchar,<br>summary nvarchar<br> <br><br>);<br>drop procedure [dbo].[employer_login]<br>create proc [dbo].[employer_login](<br><br>@username nvarchar(50),<br>@password nvarchar(50),<br>@exist int output<br><br>)<br>as<br>begin <br>if((select count (*) from employer where username = @username and password = @password) = 1)<br>begin <br>set @exist = 1<br>PRINT 'log in successfully '<br>end<br>else set @exist = 0<br>PRINT 'log in not successfully '<br>end <br>--execute<br>declare @output int<br>exec employer_login 'employer', 'employer', @output output<br><br><br>--2<br><br>create proc [dbo].[employer_register](<br>@name nvarchar(50),<br>@username nvarchar(50),<br>@password nvarchar(50),<br>@email nvarchar(50),<br>@phone nvarchar(50),<br>@companyName nvarchar(50),<br>@companyEmail nvarchar(50),<br>@companyAddress nvarchar(50),<br>@company_fax nvarchar(50),<br>@companyPhone nvarchar(50),<br>@companyField nvarchar(50),<br>@company_Description nvarchar(50)<br>)<br>as<br>begin <br>if((select count (*) from employer where username = @username)=1)<br>begin <br>PRINT 'This account was used! Please choose another one!'<br>END<br>else <br>set @exist = 0<br>BEGIN<br>PRINT 'You can use this account!'<br>--- kiem tra email hop. le.<br><br>if(@email IS NOT NULL)<br>set @email=<br>(<br>CHARINDEX(' ',LTRIM(RTRIM())) = 0 -- No embedded spaces<br>AND LEFT(LTRIM([Email]),1) &lt;&gt; '@' -- '@' can't be the first character of an email address<br>AND RIGHT(RTRIM([Email]),1) &lt;&gt; '.' -- '.' can't be the last character of an email address<br>AND LEN(LTRIM(RTRIM([Email]))) - LEN(REPLACE(LTRIM(RTRIM([Email])),'@','')) = 1 -- Only one '@' sign is allowed<br>AND CHARINDEX('.',REVERSE(LTRIM(RTRIM([Email])))) &gt;= 3<br>AND (CHARINDEX('.@',[Email]) = 0 AND CHARINDEX('..',[Email]) = 0) -- can't have patterns like '.@' and '..'<br>)<br>print 'acceptable'<br>insert into employer value() <br>end<br><br>--execute<br>exec employer_register 'employer', 'employer','employer','employer' ,'employer' ,<br>'employer' ,'employer', 'employer' ,'employer' ,'employer' ,'employer' ,'employer'<br><br>--3<br>create proc [dbo].[Candidate_login](<br><br>@username nvarchar(50),<br>@password nvarchar(50),<br>@exist int output<br><br>)<br>as<br>begin <br>if((select count (*) from Candidate where username = @username and password = @password) = 1)<br>begin <br>set @exist = 1<br>PRINT 'log in successfully '<br>end<br>else set @exist = 0<br>PRINT 'log in not successfully '<br>end <br>--execute<br>declare @output int<br>exec Candidate_login 'Candidate', 'Candidate', @output output<br><br><br>--4<br>create proc [dbo].[Candidate_register](<br>@name nvarchar(50),<br>@username nvarchar(50),<br>@password nvarchar(50),<br>@email nvarchar(50),<br>@phone nvarchar(50),<br>@companyName nvarchar(50),<br>@companyEmail nvarchar(50),<br>@companyAddress nvarchar(50),<br>@company_fax nvarchar(50),<br>@companyPhone nvarchar(50),<br>@companyField nvarchar(50),<br>@company_Description nvarchar(50),<br>@exist int output<br><br>)<br>as<br>begin <br>DECLARE @invalChars varchar(5),@valid int,@badChar varchar(1),@atPos<br>int,@periodPos int<br>SET @valid = 1<br>SET @invalChars = ' /:,;'<br>--Check to see if it's blank<br>IF len(ltrim(rtrim(@email))) = 0<br> SET @valid = 0<br>ELSE<br> --Loop invalid characters to see if it exists in email<br> WHILE len(@invalChars) &gt; 0<br> BEGIN<br> SET @badChar = substring(@invalChars,1,1)<br> IF(charindex(@badChar,@email) &gt; 0)<br> --If invalid character was found, return 0 to invalidate<br> SET @valid = 0<br> SET @invalChars = replace(@invalChars,@badChar,'')<br> END<br> --Check to see if "@" exists.<br> SET @atPos = charindex('@',@email,1)<br> IF @atPos = 0<br> SET @valid = 0<br> --Check to see if extra "@" exists after 1st "@".<br> IF charindex('@',@email,@atPos+1) &gt; 0<br> SET @valid = 0<br> SET @periodPos = charindex('.',@email,@atPos)<br> IF @periodPos = 0<br> SET @valid = 0<br> IF (@periodPos+3) &gt; len(@email)<br> SET @valid = 0<br> RETURN (@valid)<br>END<br><br><br>--5<br>create proc [dbo].[Admin_login](<br><br>@username nvarchar(50),<br>@password nvarchar(50),<br>@exist int output<br><br>)<br>as<br>begin <br>if((select count (*) from Admin where username = @username and password = @password) = 1)<br>begin <br>set @exist = 1<br>PRINT 'log in successfully '<br>end<br>else set @exist = 0<br>PRINT 'log in not successfully '<br>end <br>--execute<br>declare @output int<br>exec Admin_login 'Candidate', 'Candidate', @output output</font><font color="midnightblue" face="Verdana, Arial, Helvetica" size="2"><span class="spnMessageText" id="msg"><pre id="code"><font id="code" face="courier" size="2">SET NOCOUNT ON<br>go<br>create database human_resource_management;<br>use human_resource_management;<br>create table CV(<br>cvID int,<br>candidateID int,<br>can_img image,<br>working_area nvarchar,<br>working_place nvarchar,<br>position nvarchar,<br>workingTime nvarchar,<br>salary nvarchar,<br>degree nvarchar,<br>chung_chi nvarchar,<br>university nvarchar,<br>uni_address nvarchar,<br>falcuty nvarchar,<br>graduated nvarchar,<br>used_job nvarchar,<br>num_experience nvarchar,<br>description nvarchar,<br>submitTime nvarchar,<br>ngon_ngu nvarchar<br> <br><br>);<br>create table candidate(<br>candidateID int,<br>fullname nvarchar,<br>sex nvarchar,<br>DOB nvarchar,<br>marriage_stt nvarchar,<br>address nvarchar,<br>distrist nvarchar,<br>city nvarchar,<br>phone int,<br>email nvarchar,<br>username nvarchar,<br>password nvarchar,<br>candidateStatus nvarchar,<br>blocked nvarchar,<br>time_chstt nvarchar<br> <br><br>);<br>create table employer(<br>employerID int,<br>em_img image,<br>fullname nvarchar,<br>sex nvarchar,<br>username nvarchar,<br>password nvarchar,<br>companyName nvarchar,<br>companyEmail nvarchar,<br>companyAddress nvarchar,<br>company_fax nvarchar,<br>companyPhone nvarchar,<br>companyField nvarchar,<br>company_Description nvarchar,<br>website nvarchar,<br>timeRegister nvarchar,<br>lastVisit nvarchar,<br>blocked nvarchar<br> <br><br>);<br>create table quantri(<br>ID int,<br>fullname nvarchar,<br>username nvarchar,<br>password nvarchar,<br>bac nvarchar<br> <br><br>);<br>create table FavouriteCandidate(<br>employerID int,<br>candidateID int,<br>addedTime nvarchar<br> <br><br>);<br>create table news(<br>newsID int,<br>newsTitle nvarchar,<br>newsContent nvarchar,<br>author nvarchar,<br>timepost nvarchar,<br>summary nvarchar<br> <br><br>);<br>drop procedure [dbo].[employer_login]<br>create proc [dbo].[employer_login](<br><br>@username nvarchar(50),<br>@password nvarchar(50),<br>@exist int output<br><br>)<br>as<br>begin <br>if((select count (*) from employer where username = @username and password = @password) = 1)<br>begin <br>set @exist = 1<br>PRINT 'log in successfully '<br>end<br>else set @exist = 0<br>PRINT 'log in not successfully '<br>end <br>--execute<br>declare @output int<br>exec employer_login 'employer', 'employer', @output output<br><br><br>--2<br><br>create proc [dbo].[employer_register](<br>@name nvarchar(50),<br>@username nvarchar(50),<br>@password nvarchar(50),<br>@email nvarchar(50),<br>@phone nvarchar(50),<br>@companyName nvarchar(50),<br>@companyEmail nvarchar(50),<br>@companyAddress nvarchar(50),<br>@company_fax nvarchar(50),<br>@companyPhone nvarchar(50),<br>@companyField nvarchar(50),<br>@company_Description nvarchar(50)<br>)<br>as<br>begin <br>if((select count (*) from employer where username = @username)=1)<br>begin <br>PRINT 'This account was used! Please choose another one!'<br>END<br>else <br>set @exist = 0<br>BEGIN<br>PRINT 'You can use this account!'<br>--- kiem tra email hop. le.<br><br>if(@email IS NOT NULL)<br>set @email=<br>(<br>CHARINDEX(' ',LTRIM(RTRIM([Email]))) = 0 -- No embedded spaces<br>AND LEFT(LTRIM([Email]),1) &lt;&gt; '@' -- '@' can't be the first character of an email address<br>AND RIGHT(RTRIM([Email]),1) &lt;&gt; '.' -- '.' can't be the last character of an email address<br>AND LEN(LTRIM(RTRIM([Email]))) - LEN(REPLACE(LTRIM(RTRIM([Email])),'@','')) = 1 -- Only one '@' sign is allowed<br>AND CHARINDEX('.',REVERSE(LTRIM(RTRIM([Email])))) &gt;= 3<br>AND (CHARINDEX('.@',[Email]) = 0 AND CHARINDEX('..',[Email]) = 0) -- can't have patterns like '.@' and '..'<br>)<br>print 'acceptable'<br>insert into employer value() <br>end<br><br>--execute<br>exec employer_register 'employer', 'employer','employer','employer' ,'employer' ,<br>'employer' ,'employer', 'employer' ,'employer' ,'employer' ,'employer' ,'employer'<br><br>--3<br>create proc [dbo].[Candidate_login](<br><br>@username nvarchar(50),<br>@password nvarchar(50),<br>@exist int output<br><br>)<br>as<br>begin <br>if((select count (*) from Candidate where username = @username and password = @password) = 1)<br>begin <br>set @exist = 1<br>PRINT 'log in successfully '<br>end<br>else set @exist = 0<br>PRINT 'log in not successfully '<br>end <br>--execute<br>declare @output int<br>exec Candidate_login 'Candidate', 'Candidate', @output output<br><br><br>--4<br>create proc [dbo].[Candidate_register](<br>@name nvarchar(50),<br>@username nvarchar(50),<br>@password nvarchar(50),<br>@email nvarchar(50),<br>@phone nvarchar(50),<br>@companyName nvarchar(50),<br>@companyEmail nvarchar(50),<br>@companyAddress nvarchar(50),<br>@company_fax nvarchar(50),<br>@companyPhone nvarchar(50),<br>@companyField nvarchar(50),<br>@company_Description nvarchar(50),<br>@exist int output<br><br>)<br>as<br>begin <br>DECLARE @invalChars varchar(5),@valid int,@badChar varchar(1),@atPos<br>int,@periodPos int<br>SET @valid = 1<br>SET @invalChars = ' /:,;'<br>--Check to see if it's blank<br>IF len(ltrim(rtrim(@email))) = 0<br> SET @valid = 0<br>ELSE<br> --Loop invalid characters to see if it exists in email<br> WHILE len(@invalChars) &gt; 0<br> BEGIN<br> SET @badChar = substring(@invalChars,1,1)<br> IF(charindex(@badChar,@email) &gt; 0)<br> --If invalid character was found, return 0 to invalidate<br> SET @valid = 0<br> SET @invalChars = replace(@invalChars,@badChar,'')<br> END<br> --Check to see if "@" exists.<br> SET @atPos = charindex('@',@email,1)<br> IF @atPos = 0<br> SET @valid = 0<br> --Check to see if extra "@" exists after 1st "@".<br> IF charindex('@',@email,@atPos+1) &gt; 0<br> SET @valid = 0<br> SET @periodPos = charindex('.',@email,@atPos)<br> IF @periodPos = 0<br> SET @valid = 0<br> IF (@periodPos+3) &gt; len(@email)<br> SET @valid = 0<br> RETURN (@valid)<br>END<br><br><br>--5<br>create proc [dbo].[Admin_login](<br><br>@username nvarchar(50),<br>@password nvarchar(50),<br>@exist int output<br><br>)<br>as<br>begin <br>if((select count (*) from Admin where username = @username and password = @password) = 1)<br>begin <br>set @exist = 1<br>PRINT 'log in successfully '<br>end<br>else set @exist = 0<br>PRINT 'log in not successfully '<br>end <br>--execute<br>declare @output int<br>exec Admin_login 'Candidate', 'Candidate', @output output</font></pre><pre id="code">&nbsp;</pre><pre id="code"><font color="midnightblue" face="Verdana, Arial, Helvetica" size="2"><span class="spnMessageText" id="msg">Best regards !<br>Phoebe</span></font> <br></pre></span></font><br><font id="code" face="courier" size="2"><br></font></pre></span></font> <br></p>
  2. Adriaan New Member

    Why not start by looking at the errors you're getting, and try to work out what is going wrong.
    Then when there are specific details that you need help with, you can post specific questions. This way you have a better chance of getting a response.
  3. Phoebe New Member

    Dear friends !
    Well, i tried and fixed my code but i still have problems with part :Retrieve password for existing account.
    Discription:Candidate asks to return password.
    Input :Candidate’s email address
    Process:Check existence of email in the databaseIf email exists in the DB, send new passIf not, inform “This email does not exist!”
    Output: Inform returning new password for the existing account successfully or not
    Data storage: New password.
    Can anyone help me by posting here a sample T-sql code ? Thanks in advance
    Bests

Share This Page