CREATE TABLE #myTable99(
	  stepId		int
	, userId		varchar(20)
	, programmeId	int
	, success		int
)

INSERT INTO #myTable99(stepId, userId, programmeId, success)
SELECT 1, 'X002548'	, 10, 1 UNION ALL
SELECT 6, 'XXX'		, 25, 1 UNION ALL
SELECT 2, 'XXX'		, 25, 1 UNION ALL
SELECT 3, 'ZZZ'		, 25, 1 UNION ALL
SELECT 1, 'ZZZ'		, 25, 1

DECLARE @UserID varchar(20); 

SET @UserID = 'XXX'

SELECT CASE WHEN MAX(stepId) >= 5	THEN 4
									ELSE MAX(stepId)
		END yourVal
  FROM #myTable99
 WHERE userId = @UserId
   AND programmeId = 25
   AND success = 1

SET @UserID = 'ZZZ'

SELECT CASE WHEN MAX(stepId) >= 5	THEN 4
									ELSE MAX(stepId)
		END yourVal
  FROM #myTable99
 WHERE userId = @UserId
   AND programmeId = 25
   AND success = 1

DROP TABLE #myTable99
GO