Stored procedure to add a new user

Question

CREATE PROCEDURE SP_InsertUsers @Email NVARCHAR(30) , @Password NVARCHAR(20), @Nickname NVARCHAR(20) AS BEGIN SET NOCOUNT ON; IF EXISTS(SELECT UsersTable.Email, UsersTable.Login FROM UsersTable WHERE UsersTable.Email = @Email or UsersTable.Login = @Nickname) --check email BEGIN SELECT -1 AS FALSE END ELSE BEGIN INSERT INTO UsersTable(Email, Pass, Login, Data_reg) VALUES (@Email, @Password, @Nickname, GETDATE()) SELECT 1 AS TRUE END END

The procedure itself works without problems and does not allow creating a new user if the login or password already exists in the database.

But the problem is that it is impossible to apply it correctly from the program, I’m doing everything right.

 var regInfo = this.mydb.SP_InsertUsers(model.Email, model.Password, model.Login).ToList(); if (regInfo != null && regInfo.Count() != -1) { mydb.SaveChanges(); TempData["message"] = string.Format("Новая учетная запись \"{0}\" успешно создана!", model.Login.ToLower()); return RedirectToAction("Login", "Account"); } else { TempData["message_error"] = string.Format("Такой логин или e-mail уже кем-то используется"); }

How to properly perform the verification condition? Now when I enter any values, regInfo.Count () takes a value of 1, and therefore, even if you enter incorrect values, the else does not enter at all

And why did you decide that the SELECT result in the procedure will be available through the Count () property?
No matter WHAT value is returned, ONE value is always returned to you - as you observe ...
And what results from the procedure are available through c # properties?
As Akina said, one line is always returned from you, so you should check not the number of lines, but their contents.
Another option is to use the out-parameter in the procedure and check its value after the procedure is completed.
In addition, the procedure itself can return an arbitrary integer value via RETURN, this can also be used.
In general, there is plenty to choose from, and all of this is available in C # using standard tools.
and to make your current version work, simply remove "SELECT -1 AS FALSE" from the procedure altogether and turn the condition in IF

Alexander Muksimov Alexander Muksimov 1,271 one ten 18 · Accepted Answer · 2017-03-15T09:49:45

Wildly, I apologize, I do not have the opportunity for testing right now, but here is the starting point for solving your problem on the client side, unless of course the stored procedure does what you have in mind:

  int result = 0; SqlConnection con; try { con = new SqlConnection(myConnectionString); SqlCommand cmd = new SqlCommand("SP_InsertUsers", con)); //Нужно уточнить, возможно что команда должна выглядить так "Execute SP_InsertUsers(@Email, @Password, @Nickname)" cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = strEmail; cmd.Parameters.Add("@Password", SqlDbType.VarChar).Value = strPassword; cmd.Parameters.Add("@Nickname", SqlDbType.VarChar).Value = strNickname; con.Open(); object res = cmd.ExecuteScalar(); if (res != null) { result=System.Convert.ToInt16(res); } } catch(Exception ee) { //Обработка исключения ..... } finally { con.Close(); } if( result==1) { //Обработка успешного добавления } else { //Обработка когда добавить не смогли }

I think the idea is clear, as far as possible errors in the syntax and specification of the command line, I am sure that this is not a problem for you.

Then try directly using object res = cmd.ExecuteScalar (), according to the scheme I suggested.
The only thing I do not remember now and I have nowhere to see if you need to specify the list of parameters and write "EXEC" on the command line when creating a command.
I think to rewrite HP so that for example it does not return the field with the number as it is now, but let's say RETURN 0 if there
It’s just that a problem arises when you need to somehow extract in the code what the HP returns
I agree to go to chat, but in the morning, my work day is over.

Stored procedure to add a new user

1 answer 1

More articles: