I started by formatting your proc so we can read it. If you post this stuff in a code block (using IFCode shortcuts on the left when posting) it will help maintain your formatting.

ALTER PROCEDURE [dbo].[SP_CONSULTATION_DETAILS1] @AGE NUMERIC(3, 0)

,@date DATE

,@time TIME

,@SEX VARCHAR(1)

,@AMOUNT NUMERIC(6, 2)

AS

BEGIN

SELECT AMOUNT = (

CASE

WHEN DATENAME(DW, @date) = 'Sunday'

THEN CASE

WHEN @AGE <= 10

THEN 300

ELSE 700

END

WHEN DATENAME(DW, @date) = 'Saturday'

THEN CASE

WHEN @AGE <= 10

THEN 300

ELSE 500

END

ELSE CASE

WHEN @time < '06:00'

OR @time > '18:00'

THEN CASE

WHEN @AGE <= 10

THEN 200

ELSE 500

END

ELSE CASE

WHEN @AGE < 5

THEN 0

WHEN @AGE >= 5

AND @AGE <= 10

THEN 100

ELSE 200

END

END

END

)

FROM MASTEROPCONSAMT

END

It is impossible to determine what you are trying to do here. The biggest issue I see is that your stored proc is not filtering which row from MASTEROPCONSAMT. It is going to set the value for Amount for only 1 row in your table. If could post ddl, sample data and desired output for the two tables we can help.