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);
No comments:
Post a Comment