Saturday 11 August 2012

How to set a Local Variable in a store procedure and hold that value in Aspx page

Store procedure Pass a Local Varible which name is @out
ALTER PROCEDURE Ufms.[KC_DeleteGroup]
(
@Group_ID int,
@out int output

)
AS
BEGIN
    if EXISTS (SELECT Parent_Group_ID from Ufms.FMS_Groups WHERE Parent_Group_ID=@Group_ID)
     Select @out= 1
    ELSE
        BEGIN
        UPDATE Ufms.FMS_Groups SET Is_Deleted=1 Where Group_ID=@Group_ID
        END
END
==================================================
in Page behind Code in aspx page
====================================================
  public string DeleteGroups(int GroupID)
        {
            string MSG;
            SqlCommand deleteCommand = new SqlCommand("Ufms.KC_DeleteGroup");
            deleteCommand.CommandType = CommandType.StoredProcedure;
            deleteCommand.Parameters.Add(GetParameter("@Group_ID", SqlDbType.Int,                     Convert.ToInt32(GroupID)));
            deleteCommand.Parameters.Add(GetParameter("@OUT",SqlDbType.Int,null));
            deleteCommand.Parameters["@OUT"].Direction=ParameterDirection.Output;         
            ExecuteStoredProcedure(deleteCommand);         
            MSG= deleteCommand.Parameters["@OUT"].Value.ToString();         
            return MSG;
     
        }

No comments:

Post a Comment