Wednesday, June 20, 2012

How to retrieve value from stored procedure using out parameter?


SP

CREATE PROCEDURE GetPasswordOP
    (
   
      @UserName  varchar(50)= '' ,
      @Password varchar(50)= '' ,
      @RtnValue INT OUT
    )
AS
BEGIN
   IF EXISTS (SELECT * FROM tblUserPasswordDetail WHERE UserName = @UserName )
    BEGIN
        SET  @RtnValue = 1;
    END
    ELSE
    BEGIN

        insert into tblUserPasswordDetail(UserName,Password) values(@UserName,@Password)
        set  @RtnValue = SCOPE_IDENTITY()
    END  
END
-------------------------------------------------------------------------------

C# Page

  string Constr = ConfigurationManager.ConnectionStrings["MyDbConn"].ConnectionString;
        SqlConnection con = new SqlConnection(Constr);
        SqlCommand cmd = new SqlCommand("GetPasswordOP", con);
        cmd.CommandType = CommandType.StoredProcedure;

        SqlParameter parm = new SqlParameter("@UserName", SqlDbType.VarChar, 50);
        parm.Value = "abc";
        cmd.Parameters.Add(parm);

        SqlParameter parm2 = new SqlParameter("@Password", SqlDbType.VarChar, 50);
        parm.Value = "abc";
        cmd.Parameters.Add(parm2);



        SqlParameter parm3 = new SqlParameter("@RtnValue", SqlDbType.Int);
        parm3.Direction = ParameterDirection.Output; // This is important!
        cmd.Parameters.Add(parm3);

        con.Open();
        cmd.ExecuteNonQuery();
       


        // Print the output value
        string rtnvalue = cmd.Parameters["@RtnValue"].Value.ToString();
        con.Close();



No comments:

Post a Comment