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.