How to avoid duplicate records using Store Procedure.

Question

1.00/5 (1 vote)

See more:

SQL-Server-2005

I want to write a store procedure to insert data into table and also to check whether same username is already exist?
If yes,then exception throw as useralrady exist.

I have written the Store procedure as:

ALTER PROCEDURE [dbo].[Useradd]
@username varchar(20),
@pword nvarchar(20),
@empname varchar(20),
@email nvarchar(50),
@designation varchar(20),
@reportto varchar(20),
@result int output
AS
SELECT * FROM Users WHERE username= @username
BEGIN 
IF EXISTS(SELECT * FROM Users WHERE username= @username)
SET @result = 1
ELSE
SET @result = 0 
INSERT INTO Users(username,pword,empname,email,designation,reportto)
 VALUES(@username,@pword,@empname,@email,@designation,@reportto)
END

It is compilling successfully but not working in my c# code as I want.
Is it right or wrong?
How can I use this @result parameter in c# code.

Please guide me.

Posted 13-Sep-10 2:23am

fwr

Updated 2-Feb-22 23:53pm

Dalek Dave

v2

Add a Solution

Comments

TheyCallMeMrJames 13-Sep-10 9:30am

Why don't you just put a unique constraint on your username column in the table?

Dalek Dave 14-Sep-10 3:30am

Edited for Grammar and Readability.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 15522211 · Answer 1 · 2022-02-02T23:53:00

ALTER proc [dbo].[MyImp]
@username varchar(20),
@pword nvarchar(20),
@empname varchar(20),
@email nvarchar(50),
@designation varchar(20),
@isActive int

AS
BEGIN

declare @result int
If exists(select * from Users where username=@username )
begin
SET @result = -1
SELECT @result ID, 'Username is Allready Exists' Descr

end

else

begin
SET @result = 1
insert into dbo.Users(username,password,empname,email,designation)
values (@username,@pword,@empname,@email,@designation)

SELECT @result ID,@username username, 'Inserted Succesfully' Descr

end

END

Member 14126984 · Answer 2 · 2019-01-22T22:18:00

Solution 3

your code is correct just reurn the @result value and pass it to your method in C# for ex. int id=insertdetails(); where insertdetails is the method in 3 tier architecture
here you can write if condition if id is 1 "Details already existed' else"Details inserted"

ALTER PROCEDURE [dbo].[Useradd]
@username varchar(20),
@pword nvarchar(20),
@empname varchar(20),
@email nvarchar(50),
@designation varchar(20),
@reportto varchar(20),
@result int output
AS
BEGIN 
IF EXISTS(SELECT * FROM Users WHERE username= @username)
SET @result = 1
return @result
ELSE
SET @result = 0 
return @result
INSERT INTO Users(username,pword,empname,email,designation,reportto)
 VALUES(@username,@pword,@empname,@email,@designation,@reportto)
END

that's it
i think it will help you.
thanks,
Mohan Kishore.

Posted 22-Jan-19 22:18pm

Member 14126984

Comments

CHill60 23-Jan-19 5:10am

You do not return a parameter that has been defined as output - you assign a value to it and the calling code should pick it up from the parameter list. This is especially important when you consider that you can only return a type of int from a stored procedure. MS tend to just return a 0 from their stored procedures to indicate success (non-zero indicates failure). Furthermore, both you and the OP have missed the fact that you will always insert the data into the table, regardless of whether is already exists or not, because IF / ELSE can only execute a single statement. Your code will actually generate an error "Incorrect syntax near the keyword 'ELSE'"
Better would have been

ALTER PROCEDURE [dbo].[Useradd]
	@username varchar(20),
	@pword nvarchar(20),
	@empname varchar(20),
	@email nvarchar(50),
	@designation varchar(20),
	@reportto varchar(20),
	@result int output
AS
BEGIN 
	IF EXISTS(SELECT * FROM Users WHERE username= @username)
		SET @result = 1
	ELSE
	BEGIN
		SET @result = 0 
		INSERT INTO Users(username,pword,empname,email,designation,reportto)
		VALUES(@username,@pword,@empname,@email,@designation,@reportto)
	END
	return @result
END

Richard Deeming 24-Jan-19 8:44am

Actually, this version will never insert the data, since both branches return before reaching the INSERT statement. :)

CHill60 24-Jan-19 9:08am

Oh darn it. How on earth did I get that wrong? Again. Don't answer that! :-)
Thanks for correction

Sandeep Mewara · Answer 3 · 2010-09-13T06:38:00

Solution 2

Your way of declaring parameter @result is wrong.
Correct way to declare a local variable is:

SQL

DECLARE @result int -- in body after AS

Infact you don't need it based on your scenario. Try something like:

SQL

CREATE PROCEDURE [dbo].[Useradd]
      @username varchar(20),
      @pword nvarchar(20),
      @empname varchar(20),
      @email nvarchar(50),
      @designation varchar(20),
      @reportto varchar(20)
AS

  IF (SELECT COUNT(*) FROM Users WHERE username= @username) < 1
    BEGIN   
      INSERT INTO Users(username,pword,empname,email,designation,reportto)    
      VALUES(@username,@pword,@empname,@email,@designation,@reportto)
    END

UPDATE: Changed the count condition from >0 to <1

Posted 13-Sep-10 6:38am

Sandeep Mewara

Updated 13-Sep-10 23:20pm

v3

Comments

Yusuf 13-Sep-10 17:06pm

You procedure should have checked for < 0. As it is now will insert only if the username exist, which is not what the OP asked.

hitesh darji 22-Feb-14 8:05am

gud..

Sandeep Mewara 14-Sep-10 2:17am

Thanks my bad!
BTW not < 0.... it should be less than < 1 :)

fwr 14-Sep-10 3:12am

Thanks sir!! its now working properly.

Sandeep Mewara 14-Sep-10 3:35am

Good to know it helped and works.

mohangbits 14-Sep-10 4:44am

good one :)

Dalek Dave 14-Sep-10 6:23am

Good Answer.

CHill60 23-Jan-19 4:58am

OP was not declaring a local variable, they were (correctly) declared an output parameter

How to avoid duplicate records using Store Procedure.

3 solutions

Solution 5

Solution 3

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us