Click here to Skip to main content
15,392,261 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
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
Updated 2-Feb-22 23:53pm
v2
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.

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
   
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
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.
   
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
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
   

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900