Question on stored procedure
-
I have a store procedure that adds to a table below but i would like it to be able
to check if a record exists and if it exists return to user that the User already exists
Which i added below but i get error:-
Cannot insert duplicate key in object 'Users'. The statement has been terminated.
Any ideas what i'm doing wrong?
thanks
if exists
-- You cannot register usernames already registered on the database twice.
(
select username from Employees where username = @username
)
return 1 else
INSERT INTO Users
(
FullName,
EmailAddress,
Password
)
VALUES
(
@FullName,
@Email,
@Password
)
SELECT
@userid = @@Identity
-
See if these links help,
http://www.sommarskog.se/error-handling-II.html
http://www.sommarskog.se/error-handling-I.html
--------------------
Colt 45 - the original point and click interface
-
i would use a different logic and return the userid regardless of whether it existed previously or not:
--assuming proc has the @userid parameter
select @userid=username from Employees where username = @username
if @userid Is not null
SELECT @userid
ELSE
BEGIN
INSERT INTO Users
(
FullName,
EmailAddress,
Password
)
VALUES
(
@FullName,
@Email,
@Password
)
SELECT @userid = @@Identity
END
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I'd guess that
@FullName exists in Users
but
@username does not exists in [Employees]
Far away is close at hand in the images of elsewhere.
Anon. -
Thx all for the replies but i pasted the wrong Storepd procedure below is the correct one and the problem 'm facing
I get the error when i INSERT a record the second time..
Don't know what could be wrong...
The funniest thing is if i refresh the page and insert another record it
goes in fine.But the second time when its suppose to return the retunr
value i get that error..
Any ideas??
CREATE Procedure CMRC_UserAdd
(
@FullName nvarchar(50),
@Email nvarchar(50),
@Password nvarchar(50)
)
AS
if exists
-- You cannot register usernames already registered on the database
twice.
(
select FullName from CMRC_Users where FullName = @FullName
)
return 1 else
INSERT INTO CMRC_Users
(
FullName,
EmailAddress,
Password
)
VALUES
(
@FullName,
@Email,
@Password
)
GO
-
What is the datatype of FullName and why did you select nvarchar for your @FullName variable?
I wasn't born stupid - I had to study.
-
Is the error
Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object
or
Cannot insert duplicate key row in object
If it is the latter then the error is probably due to a unique index violation
What is the primary key and what indexes are on that table?
Far away is close at hand in the images of elsewhere.
Anon. -
Well, I can't reproduce this.
Can you post the DDL for the table, and also provide a sample row that gives you the error..?
As far as I can tell, the proc works as expected.
/Kenneth
-
Isn't there a problem in SQL Server where, in a stored proc, SQL statements that follow a RETURN will get executed anyway? Not sure if this is the case, but maybe you could try restructuring your logic a bit, e.g.:
- declare @retval int
- set @retval = 0
- -- You cannot register usernames already registered on the database twice.
if not exists
(select FullName from CMRC_Users where FullName = @FullName)
begin
INSERT INTO CMRC_Users
(
FullName,
EmailAddress,
Password
 
VALUES
(
@FullName,
@Email,
@Password
 
end
else
begin
set @retVal = 1
end
- RETURN @retVal
Don't have any idea if this is the problem or not, but may be worth a try.
-
OK, there's no wink character in SQL Server, you'll have to use a closing paren. Sorry about that.
-
Isn't there a problem in SQL Server where, in a stored proc, SQL statements that follow a RETURN will get executed anyway? To my knowledge, no
I have never found this with my sp's Far away is close at hand in the images of elsewhere.
Anon. -
OK, here's what I was talking about:
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=14697&p=2
Try this stored proc:
CREATE PROCEDURE dbo.StoredProcedure1 AS
Print 'Here 1'
RETURN
(Select Count(*) from INFORMATION_SCHEMA.COLUMNS)
Print 'Here 2'
RETURN
(Select Count(*) from INFORMATION_SCHEMA.TABLES )
Print 'Here 3'
RETURN -1 Print 'Here 4'
Although, oddly, the SELECT statements don't return results, the RETURN statements do not exit the stored proc.
-
Maybe
Definately indeterminate 
RETURN expects an 'integer expression', the subquery is not and neither is it's results.
Mind you, you would expect sql to error the staement
Besides this is not the case here as the RETURN is returning a constant which is an integer expression.
Far away is close at hand in the images of elsewhere.
Anon. -
It does seem that the RETURN is not functioning as per Books Online ( wouldn't be the first time
)"Although, oddly, the SELECT statements don't return results"
Isn't that because you've got them enclosed in parentheses? Having the stored procedure as follows seems to work as expected.
CREATE PROCEDURE dbo.StoredProcedure2 AS
Print 'Here 1' RETURN Select Count(*) from INFORMATION_SCHEMA.COLUMNS Print 'Here 2' RETURN Select Count(*) from INFORMATION_SCHEMA.TABLES Print 'Here 3' RETURN -1 Print 'Here 4'
--------------------
Colt 45 - the original point and click interface -
RETURN is supposed to be used with a value of type INT. It appears to misbehave when you use a resultset as the return value, which is what my example was doing (sorry about the bad formatting).
In any case, this does not appear to be the source of the original problem as the proc *is* returning an int (value of 1). Seeing the RETURN in the middle of the proc reminded me of this particular issue, but I expect that it's not really the source of the problem.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply