ksrameshbabu,

Since the variable @Temp contains multiple values you should put it in a temp TABLE variable. You can then join to it instead of using "Where in()". This works and it's fast.

Also, use a derived table to return the max ids, then join to this table on the max ids = ids (see above code).

Also, try to get rid of the cursor. If you have to loop look up "While" in BOL; it can replace most fast forward cursors.

cl

 



declare @Temp Table (PTSREFELIGID int)

Insert @Temp Values (NULL)--One row for each PTSREFELIGID



declare @PARTLOCID int



select 	pd3.AMOUNT, pd3.POINTS, pd3.ROUNDING, pd3.MAXPOINTS 

From  	(

		Select max(CAMPAIGNID) as maxCAMPAIGNID, max(CAMPAIGNSUBID) as maxCAMPAIGNSUBID

		From @Temp t 

		JOIN  POINTS_DETAILS pd1 on  pd1.PTSREFELIGID = t.PTSREFELIGID

		where pd0.PARTLOCID =@PARTLOCID

		) pd2

JOIN 	POINTS_DETAILS pd3 

			on pd2.maxCAMPAIGNID = pd3.CAMPAIGNID 

			and pd2.maxCAMPAIGNSUBID = pd3.CAMPAIGNSUBID