Click here to Skip to main content
15,031,495 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i want to amend the following stored procedure to add the count query in it..

i want to add count query . to check wheather the user exits or not...
here is the query..

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[insertUser] 
@UserName varchar(50),
@Password varchar(50),
@FirstName varchar(50),
@LastName varchar(50),
@Email varchar(150),
@Mobile varchar(50)


AS
BEGIN
	INSERT INTO users(UserName,Password,FirstName,LastName,Email,MobileNo)VALUES (@UserName,@Password,@FirstName,@LastName,@Email,@Mobile)
	
END

i want to add count query likne belo.. how can i amend the above query to add the fallowing query in it...

C#
string querycount= "Select Count(*) FROM users where username=@username

if count is zero than executing the insert query.

and also if user exits i want to show that user already exits on the record in my asp applcation label control.
Posted
Updated 27-Nov-11 1:14am
v4

Try this one:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[insertUser] 
@UserName varchar(50),
@Password varchar(50),
@FirstName varchar(50),
@LastName varchar(50),
@Email varchar(150),
@Mobile varchar(50),
@Result varchar(100) output
 

AS
BEGIN

IF((SELECT COUNT(*) FROM users WHERE UserName = @UserName) > 0)
   SET @Result = 'User already exists'
ELSE
BEGIN
	INSERT INTO users(UserName,Password,FirstName,LastName,Email,MobileNo)VALUES (@UserName,@Password,@FirstName,@LastName,@Email,@Mobile)

   SET @Result = 'User successfully added.
END 
END


Let me know if you have other concerns.

Please mark as answer if this solved your problem

Best regards,
Eduard
   
v4
Comments
codegeekalpha 27-Nov-11 6:47am
   
i am getting incorrect syntax near @result
[no name] 27-Nov-11 7:06am
   
UPDATED my solution. I forgot the SET command.
[no name] 27-Nov-11 7:09am
   
re-updated my solution. try it again.
[no name] 27-Nov-11 7:11am
   
you can use @Result in C# to display result message. Research on ParameterDirection.Output
[no name] 27-Nov-11 7:12am
   
research on parameterdirection.output for c# in order for you to get @Result in c#
SQL
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[insertUser] 
@UserName varchar(50),
@Password varchar(50),
@FirstName varchar(50),
@LastName varchar(50),
@Email varchar(150),
@Mobile varchar(50),
@Result varchar(100) output
 
 
AS
BEGIN
Declare @Count int

SELECT @Count =  COUNT(UserName) FROM users WHERE UserName = @UserName
if (@Count = 0)
begin
	INSERT INTO users(UserName,Password,FirstName,LastName,Email,MobileNo)VALUES (@UserName,@Password,@FirstName,@LastName,@Email,@Mobile)
set @Result ='User Added successfully'
end
else
  set @Result = 'User Already Exists'
	
END


Please mark as answer if this solved your problem
   
v2
Comments
Aja wakim 27-Nov-11 7:15am
   
looks like a duplicate od solution1. my vote of 1

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