A stored procedure was written with the SQL Server database, the code is as follows
<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" >create procedure Proc_select @id intasbeginif exists (SELECT * from news where [email protected]) return 1elsereturn 2en D</span>
In C #, you get the return value by executing a stored procedure, but the returned result is always-1, tangled. Query in the database, there is no problem. The stored procedure is also correct. It is also no problem to test the stored procedure in the database.
What is wrong with that? Why does it return-1?
My code is like this.
<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" >private void Button1_Click (object sender, EventArgs e) { try { string constr = "server= 192.168.24.235;database=newssystem;uid=sa;pwd=1 "; SqlConnection conn = new SqlConnection (CONSTR); SqlCommand cmd = new SqlCommand (); Cmd.commandtext = "Proc_select"; Cmd.commandtype = CommandType.StoredProcedure; Cmd. Connection = conn; Conn. Open (); SqlParameter sp = new SqlParameter ("@id", "5"); Cmd. Parameters.Add (SP); TextBox1.Text = cmd. ExecuteNonQuery (). ToString ();</span>
That would be the equivalent of doing it in SQL.
Later after the Internet query, if the database execution returned is "command completed successfully", then returned is-1, OK ... But there is also the case that if a rollback occurs, the return value is also-1. That's not funny, so how do I tell if my stored procedure was successful? How do you get the correct return value for a stored procedure?
After the Internet query, the original add a few lines of code can be.
<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" > private void Button1_Click (object sender, EventArgs e) {try {string con STR = "Server=192.168.24.235;database=newssystem;uid=sa;pwd=1"; SqlConnection conn = new SqlConnection (CONSTR); SqlCommand cmd = new SqlCommand (); Cmd.commandtext = "Proc_select"; Cmd.commandtype = CommandType.StoredProcedure; Cmd. Connection = conn; Conn. Open (); SqlParameter sp = new SqlParameter ("@id", "5"); Cmd. Parameters.Add (SP); SqlParameter returnvalue = new SqlParameter ("@returnValue", SqlDbType.Int); Returnvalue.direction = ParameterDirection.ReturnValue; Cmd. Parameters.Add (returnvalue); Cmd. ExecuteNonQuery (); TextBox1.Text = ReturnValue.Value.ToString (); Conn. Close (); } catch (Exception ex) {Throw ex; }}</span>
A return parameter was added to the code, and the return value was successfully resolved. haha ~ Progress again!
Database operations--Get the return value of a stored procedure