首个存储过程实践-优快云博客

作者分享了自己使用ASP.NET C#完成的第一个存储过程的开发经历，包括存储过程的创建及调用方式，并记录了开发过程中的感受。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天终于完成了我的第一个存储过程，对我这个老青年来说真不容易啊。

CREATE PROCEDURE [dbo].[createRole]
@RoleName varchar(50),
@Result varchar(50) output
AS
If not Exists(SELECT RoleName FROM Roles WHERE RoleName = @RoleName)
  Begin INSERT INTO Roles (RoleName) values (@RoleName)
  SET @Result = '角色创建成功!'
  End
ELSE SET @Result = '该角色已存在!'

程序调用：ASP.NET C#

string strConn = ConfigurationManager.ConnectionStrings["myConnectionString"].ToString();
SqlConnection myconn = new SqlConnection(strConn);

//String strSql = string.Format("EXEC dbo.createRole {0}", RoleTextbox.Text);

        SqlCommand cmd = new SqlCommand("dbo.createRole", myconn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@RoleName", SqlDbType.VarChar, 50));
        cmd.Parameters["@RoleName"].Value = RoleTextbox.Text;
        cmd.Parameters.Add(new SqlParameter("@Result", SqlDbType.VarChar, 50));
        cmd.Parameters["@Result"].Direction = ParameterDirection.Output;
        try
        {
            myconn.Open();
            cmd.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        finally
        {
            myconn.Close();
        }
        MessageLabel.Text = cmd.Parameters["@Result"].Value.ToString();

//////////////////////////////////下面这段是超简便的

string strConn = ConfigurationManager.ConnectionStrings["myConnectionString"].ToString();
SqlConnection myconn = new SqlConnection(strConn);

string sql = string.Format("EXEC dbo.createUser {0},{1},{2},{3}, @Result OUTPUT",UserNameTextbox.Text,PasswordTextbox.Text,DropDownList_Corp.Text,DropDownList_Role.Text);
SqlCommand cmd = new SqlCommand(sql, myconn);

            cmd.Parameters.Add(new SqlParameter("@Result", SqlDbType.VarChar, 50));
            cmd.Parameters["@Result"].Direction = ParameterDirection.Output;
            try
            {
                myconn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                myconn.Close();
            }
            MessageLabel.Text = cmd.Parameters["@Result"].Value.ToString();