Jay-246689 (3/20/2013)

---

Thanks for all the reply, i been focus on other things and just read your messages. So it seems it is already optimized (Casting). Yes, on the latter part they used it to do insert/update on this field and some cases use it as a condition.

For the cursor here below is the scripts. This is created as scalar-valued functions, and being called multiples times in one process, >5millions calls. The results/returns records of each cursor called will not be more than 10 records and most of the times, 95%, it will only return one record. dbo.table holds more or less 800K records. And there are only 4 objects dependent on this function. Can you suggest any optimization on this scripts.

-----------------

DECLARE @m INT

SET @m = 0

DECLARE @l INT

SET @l = 0

DECLARE l_cursor CURSOR FOR

SELECT LId

FROM [dbo].table

WHERE EH = @param1

ORDER BY LId

OPEN l_cursor FETCH NEXT FROM l_cursor INTO @l

WHILE @@FETCH_STATUS = 0

BEGIN

IF @l = 0 SET @m = @m | 0x0001

ELSE IF @l = 1 SET @m = @m | 0x0009

ELSE IF @l = 2 SET @m = @m | 0x0011

........multiple else if

FETCH NEXT FROM l_cursor

INTO @l

END

CLOSE l_cursor

DEALLOCATE l_cursor

return @m;

--------------

Thanks again.