hello dears,
I create a stored procedure as follow,
In this store procedure I want to insert user registration details in tblUserInfo.
if email Id is not exist in database then I want to return 1 from stored procedure and save data in database that I have passed to stored procedure.
if email is already exist in database.. I want to return 2 from stored procedure and data dont want to save.
I tried following stored procedure but it produce unexpected result. How can I do it.
ALTER PROCEDURE [dbo].[userRegistration]
@userId VARCHAR(12),
@emailadr VARCHAR(50),
@password VARCHAR(12)
AS
BEGIN
DECLARE @userAutoId VARCHAR(64)=NEWID();
DECLARE @firstName VARCHAR(15)='';
DECLARE @lastName VARCHAR(15)='';
DECLARE @gender BIT=1;
DECLARE @isDeleted BIT= 0;
DECLARE @Status INTEGER;
IF NOT EXISTS(SELECT * FROM tblUserInfo WHERE email=@emailadr)
BEGIN
INSERT INTO tblUserInfo VALUES(
@userAutoId,
@firstName,
@lastName,
@emailadr,
@userid,
@password,
@gender,
@isDeleted
)
SET @Status=1
END
ELSE
BEGIN
SET @Status=2
END
SELECT @Status
END
and my c# code is
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);
con.Open();
SqlCommand cmd = new SqlCommand("userRegistration ", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@userId", SqlDbType.VarChar).Value = userId;
cmd.Parameters.Add("@emailadr", SqlDbType.VarChar).Value = emailadr;
cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = password;
SqlDataReader rdrStatus = cmd.ExecuteReader();
if (rdrStatus[0].ToString() == "1")
{
string result = "1";
json = new JavaScriptSerializer().Serialize(result);
}
else if (rdrStatus[0].ToString() == "1")
{
string result = "2";
json = new JavaScriptSerializer().Serialize(result);
}
con.Close();
return json;
}
What I have tried:
I am trying to new user registration. don't able to use already used email id