存储过程检查/比较列的值

Question

VS码

try
{
    DBContext cl = new DBContext();
    SqlConnection cn = new SqlConnection(DBContext.ConnectionString);
    SqlCommand cmd = new SqlCommand("UERMEMR..ys_Update_Borrow", cn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("BORROWING_ID", txtbox_borrow_ID.Text);

    cn.Open();
    cmd.ExecuteNonQuery();
    cn.Close();
    MessageBox.Show("Closed Successfully", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);

    txtbox_borrow_ID.Clear();
    {
        DLPatients p1 = new DLPatients();
        dt = p1.GetRecord();
        this.Grid_borrow2.SetDataBinding(dt, "", true);
    }
}
catch (Exception userEx)
{
    MessageBox.Show("Error : " + userEx.Message);
}

SP代码(我会修改)

CREATE PROCEDURE [dbo].[ys_Update_Borrow]
(@BORROWING_ID int,
@STATUS varchar (50) = 'Closed',
@DATE_CLOSED datetime = null)

AS
UPDATE MEDREC_BORROWING
SET STATUS = @STATUS,
    DATE_CLOSED = COALESCE(@DATE_CLOSED , GETDATE())
FROM MEDREC_BORROWING
WHERE BORROWING_ID = @BORROWING_ID
GO

我想首先检查状态是否已关闭，并将一条消息或错误输出到VS。如果不关门，就去我的SP。

请帮帮忙。

Answer 1

我想先检查状态是否关闭。

好的，您希望检查Status = 'Closed'是否简单地通知用户(或显示错误)，但是如果它未关闭，则需要使用存储过程关闭它。您的存储过程代码已经存在。

下面是如何首先检查：

var command = new System.Data.SqlClient.SqlCommand();
command.CommandType = CommandType.Text;
command.Parameters.Add("BORROWING_ID", SqlDbType.VarChar).Value = txtbox_borrow_ID.Text;
command.CommandText = "SELECT STATUS FROM MEDREC_BORROWING WHERE BORROWING_ID = @BORROWING_ID ";
string status  = command.ExecuteScalar().ToString();
if (status == "Closed")
{
    MessageBox.Show("Already closed!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
    // put your stored procedure code here and call it
}