This is the stored procedure
ALTER PROCEDURE [dbo].[Usp_purging_updateretentionmth]
@newMonth INT,
@currentMonth INT OUTPUT
AS
BEGIN
SET nocount ON;
UPDATE tbl_purgingmonth
SET currentstatus = 'Archive'
WHERE currentstatus = 'Active'
INSERT INTO tbl_purgingmonth
VALUES ( @newMonth, Getdate(), system_user, 'Active' )
SET @currentMonth = @newMonth
END
To retrieve output value, you must use this code
command.Parameters["@currentMonth"].Value
1: // Add the input parameter and set its properties.
2: SqlParameter pNewMonth = new SqlParameter();
3: pNewMonth.ParameterName = "@newMonth";
4: pNewMonth.SqlDbType = SqlDbType.Int; 5: pNewMonth.Direction = ParameterDirection.Input; 6: pNewMonth.Value = newMonth; 7: 8: // Add the output parameter and set its properties.
9: SqlParameter pCurrentMonth = new SqlParameter();
10: pCurrentMonth.ParameterName = "@currentMonth";
11: pCurrentMonth.SqlDbType = SqlDbType.Int; 12: pCurrentMonth.Direction = ParameterDirection.Output; 13: 14: //Add the parameter to the Parameters collection.
15: command.Parameters.Add(pNewMonth); 16: command.Parameters.Add(pCurrentMonth); 17: 18: command.ExecuteScalar(); 19: 20: mth = Convert.ToInt32(command.Parameters["@currentMonth"].Value);
