adding count in stored procedure with the insert query.

Question

0.00/5 (No votes)

See more:

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 27-Nov-11 0:15am

codegeekalpha

Updated 27-Nov-11 1:14am

Eduard Lu

v4

Add a Solution

2 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)

Eduard Lu · Accepted Answer · 2011-11-27T00:30:00

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

sagar15modi · Accepted Answer · 2011-11-27T00:47:00

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

adding count in stored procedure with the insert query.

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0