Database Operations -- get the returned values of stored procedures and returned values of Stored Procedures
Write a stored procedure using 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 id=@id)return 1elsereturn 2end</span>
In C #, the returned value is obtained by executing the stored procedure, but the returned result is always-1, tangled. There is no problem in querying databases. The stored procedure is correct. There is no problem in testing and modifying the stored procedure in the database.
So what is wrong? Why is-1 returned?
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>
This is equivalent to executing in SQL.
After the query, if the database returns "the command has been successfully completed", the returned result is-1, okay... However, if a rollback occurs, the return value is-1. This is not fun. How can I determine if my stored procedure has been successfully executed? How can I obtain the correct return value of a stored procedure?
After online query, you can add a few lines of code.
<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); 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>
If a return parameter is added to the Code, the return value is successfully solved. Haha ~ Again!