January 4, 2011 at 10:26 am
Dear friends !
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.
Below is my code with many error,please help me check and fix it. Thanks in advance
SET NOCOUNT ON
go
create database human_resource_management;
use human_resource_management;
create table CV(
cvID int,
candidateID int,
can_img image,
working_area nvarchar,
working_place nvarchar,
position nvarchar,
workingTime nvarchar,
salary nvarchar,
degree nvarchar,
chung_chi nvarchar,
university nvarchar,
uni_address nvarchar,
falcuty nvarchar,
graduated nvarchar,
used_job nvarchar,
num_experience nvarchar,
description nvarchar,
submitTime nvarchar,
ngon_ngu nvarchar
);
create table candidate(
candidateID int,
fullname nvarchar,
sex nvarchar,
DOB nvarchar,
marriage_stt nvarchar,
address nvarchar,
distrist nvarchar,
city nvarchar,
phone int,
email nvarchar,
username nvarchar,
password nvarchar,
candidateStatus nvarchar,
blocked nvarchar,
time_chstt nvarchar
);
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
);
create table quantri(
ID int,
fullname nvarchar,
username nvarchar,
password nvarchar,
bac nvarchar
);
create table FavouriteCandidate(
employerID int,
candidateID int,
addedTime nvarchar
);
create table news(
newsID int,
newsTitle nvarchar,
newsContent nvarchar,
author nvarchar,
timepost nvarchar,
summary nvarchar
);
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.
if(@email IS NOT NULL)
set @email=
(
CHARINDEX(' ',LTRIM(RTRIM([Email]))) = 0 -- No embedded spaces
AND LEFT(LTRIM([Email]),1) <> '@' -- '@' can't be the first character of an email address
AND RIGHT(RTRIM([Email]),1) <> '.' -- '.' can't be the last character of an email address
AND LEN(LTRIM(RTRIM([Email]))) - LEN(REPLACE(LTRIM(RTRIM([Email])),'@','')) = 1 -- Only one '@' sign is allowed
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM([Email])))) >= 3
AND (CHARINDEX('.@',[Email]) = 0 AND CHARINDEX('..',[Email]) = 0) -- can't have patterns like '.@' and '..'
)
print 'acceptable'
insert into employer value()
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
Best regards !
Phoebe
January 4, 2011 at 10:32 am
Do you have specific questions or problems you're running into?
I see some tables, and some procs that access those tables, but I can't tell what the business rules are for the procs, so I can't tell if they are designed correctly or not. Are you getting error messages from them, or something else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 4, 2011 at 11:38 am
Dear GSquared !
I have encountered with many syntax errors in the code i posted above. Below is the attached file about Use case specification that i must follow and use procedure to test these use cases. Please have a look at this document.
Hopefully you can help me fix errors and i can finish this project.
Thanks in advance ^^
Warmly
January 4, 2011 at 12:35 pm
i copied and pasted your code into SSMS, and your syntax errors all seem to revolve around two issues: every CREATE PROC... statement must be in it's own batch...adding a GO statmenet before each them fixes that issue.
the second issue is the procedure [dbo].[employer_register] references the variable @exist, which was declare outside of the procedure...so you'll have to fix that.
after putting the GO statements in place, SSMS pretty much points you directly to the actual, specific errors...there's only 5 of them...should be easy to clear up.
Lowell
January 4, 2011 at 12:38 pm
also, this is just incorrect...maybe a copy paste error?
@email is declared as a nvarchar(50), but you seem to be assigning it the results of a boolean true / false mess that doesn't make sense.
if(@email IS NOT NULL)
set @email=
(
CHARINDEX(' ',LTRIM(RTRIM([Email]))) = 0 -- No embedded spaces
AND LEFT(LTRIM([Email]),1) <> '@' -- '@' can't be the first character of an email address
AND RIGHT(RTRIM([Email]),1) <> '.' -- '.' can't be the last character of an email address
AND LEN(LTRIM(RTRIM([Email]))) - LEN(REPLACE(LTRIM(RTRIM([Email])),'@','')) = 1 -- Only one '@' sign is allowed
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM([Email])))) >= 3
AND (CHARINDEX('.@',[Email]) = 0 AND CHARINDEX('..',[Email]) = 0) -- can't have patterns like '.@' and '..'
)
Lowell
January 4, 2011 at 1:34 pm
I think you may find you have lots of data structure issues. All your columns are declared as nvarchar. This means they can't hold more than 1 character. Also do you really need to use nvarchar for things like website and email addresses? I would suggest backing up and using proper datatypes and then fixing your sprocs.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 5, 2011 at 5:54 am
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 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.
Can anyone help me by posting here a sample T-sql code ? Thanks in advance
Bests
January 5, 2011 at 7:37 am
Just my opinion but that sounds like a system requirement flaw and not a coding problem. Passwords should be stored encrypted and not retrievable. There should be a mechanism in place to have it reset with a challenge / response. This way if your data is compromised you have done your part to protect your users. I know that some will argue this point when the system or the data is not sensitive but in reality most people (I mean typical users here, not tech users) have maybe 2-3 passwords that they use for every system where they have an account. This is a debate that has no right or wrong answer but I fall on the side of better safe than sorry. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 5, 2011 at 7:45 am
If this is for school/homework, you should be working through some of this yourself. The scenarios you present are not that difficult, and some experimentation should help you.
If this is for a job, then you are asking for consulting and should pay. We are more than happy to help, but to a large extent, you haven't really made an effort to solve these yourself.
January 7, 2011 at 6:58 am
Dear Lowell and Sean Lange !
I've just finished my project and hand in it to my teacher 🙂 Thank you very much for regarding and helpful advices . It is the first time i work with many types of stored procedure and cope with many difficulties(for me, i thought so). Again, thank you very much for your help
Warmly
April 18, 2016 at 3:09 am
Hi, I have weird issue with alter stored procedure designer:
each time open modify window designer add below constraints from tables affected.
Examlple:
/****** Object: StoredProcedure [dbo].[admin_Total] Script Date: 04/18/2016 11:43:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[admin_Total]
AS
BEGIN
SET NOCOUNT ON;
SELECT Total, [sent] as [Sent],person as [Person] from Total
END
GO
ALTER TABLE [dbo].[Total] ADD CONSTRAINT [DF_Total_sent] DEFAULT ((0)) FOR [sent]
what settings I've missed or set occasionally in tools
Thanx,
April 18, 2016 at 3:17 am
Is this relevant to the thread you've posted in? If not, please start a new one.
It's not clear what your question is. If you're expecting the ALTER TABLE statement to form part of the stored procedure definition, you need to remove the GO. But I don't recommend putting DDL statements in stored procedures.
John
April 18, 2016 at 3:56 am
Already found the answer,
Generate dependent objects set to false
Thanx.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply