Thanks to mtassin and HowardW.

Both of you are correct, "Parameter Sniffing" is what happening with my procedure. Now my problem is solved.

DECLARE @tmp_Dates TABLE

(

Date1 datetime

,Date2 datetime

)

INSERT INTO @tmp_Dates VALUES ('1/1/2009','1/1/2009')

INSERT INTO @tmp_Dates VALUES (NULL,'1/2/2009')

INSERT INTO @tmp_Dates VALUES (NULL,'1/3/2009')

INSERT INTO @tmp_Dates VALUES (NULL,'1/4/2009')

INSERT INTO @tmp_Dates VALUES ('1/5/2009','1/5/2009')

INSERT INTO @tmp_Dates VALUES (NULL,'1/6/2009')

INSERT INTO...

Try This way

DECLARE @Temp Table

(TimeTaken CHAR(5)

)

INSERT INTO @Temp

SELECT '00100'

UNION ALL

SELECT '00125'

UNION ALL

SELECT '00030'

UNION ALL

SELECT '00030'

UNION ALL

SELECT '00100'

UNION ALL

SELECT '00100'

UNION ALL

SELECT '00100'

UNION ALL

SELECT '00030'

SELECT SUM(CONVERT(INT,SUBSTRING(TimeTaken,1,3)))

+ SUM(CONVERT(INT,SUBSTRING(TimeTaken,4,2)))/60 Hrs,

...

May I know what is the data type used for column TimeTaken

Try this

DECLARE @WeekNumINT =3,

@YearINT = 2009,

@StartDayOfWeekDATETIME,

@FirstJanDATETIME

SELECT @FirstJan = CONVERT(DATETIME,'1Jan'+CONVERT(NVARCHAR(4),@Year))

IF (@WeekNum = 1)

BEGIN

SELECT @StartDayOfWeek = @FirstJan

END

ELSE

BEGIN

SELECT @StartDayOfWeek = @FirstJan +(7-DATEPART(dw,@FirstJan)+1)+(@WeekNum-2)*7

END

SELECT @StartDayOfWeek AS StartDayOfWeek

Hope you understood..

ok Adi and I'll go through the Isolation Levels.

Yes Mr.Adi I am using SqlServer2008

Hi,

Your Requirement is fulfilled easily in SQLserver2008.

In SQLserver2008 there is a concept called Merge.with this the performane is also improoved.

Try the following query.

MERGE INTO TableB AS Target

USING TableA AS...