• There is a big difference in writing :

    >> Unoptimsed SP method for lookup using local var

    create procedure mysum (@myid int)

    as

    declare @myint int

    set @myint= @myid

    select x,y,z from mytable

    where mylookupfilter = @myint

    and:

    >>Optimised using input variable allows select statement

    >> to be properly cached

    create procedure mysum (@myid int)

    as

    select x,y,z from mytable

    where mylookupfilter = @myid

    If to test the true performance, would need to correctly structure the sp to use the input parameter on the where clause filter, otherwise the query is not cached the same. I suspect you will find performance of the set-based sql procedure will improve even further

    My mantras always been : "functions like cursors should be avoided if at all possible".

    I have certainly seen a good benefit of removing functions and converting to pure optimsed set based sql solutions.