tsql - SQL Server 2005: Procedure much slower than function -

edits:
1. string literals have n prepended on them, e.g. n'' , n'%'. has had no impact on timing.
2. including links execution plans.

execution plan function: https://drive.google.com/file/d/0bxunjkb4d8y6elv1ohvuawvruu0/edit?usp=sharing

execution plan procedure: https://drive.google.com/file/d/0bxunjkb4d8y6cm9sszfznwdovke/edit?usp=sharing

i'm trying re-implement inline table-valued function stored procedure, can dynamically control columns compare while searching (you can see details below). unfortunately, procedure many (4-5) orders of magnitude slower function, makes unusable, i'm coming here guidance. can see procedure guts below, have accounted parameter sniffing (via with recompile), , i'm pretty sure i've accounted ansi_nulls.

my question is, "why stored procedure slower function, , how speed @ least fast?"

before comes up, reason terribly slow double-wildcard searching underlying view cannot indexed multiple reasons, such employing left outer join, can't employ more clever methods rely on indexing.

the following block takes approximately 12 seconds.

exec [db_name].[schema_name].[procedure_name]  @foo='richard', @bar='j', @spam='smith', @eggs='jr'; 

the following block takes approximately 0 seconds.

select * [db_name].[schema_name].[function_name] ('richard','j','smith','jr') 

guts follow below. execution plans 2 differ widely, although not competent enough @ sql understand or impact of many differences.

i happy share information view, if it's relevant; example, can tell isn't indexed, , contains calculated columns using stuff (since not have access proper concatenation aggregate function) , isnull, , employs select distinct.

honestly, have no idea relevant, since both stored procedure , function work on same view similar code.

guts of procedure:

use [db_name] go set ansi_nulls on go set quoted_identifier on go alter procedure [schema_name].[procedure_name]     @foo nvarchar(max) = null     ,@bar nvarchar(max) = null     ,@spam nvarchar(max) = null     ,@eggs nvarchar(max) = null recompile begin     set nocount on;     declare @sqlcmd nvarchar(max);     set @sqlcmd = n'         select distinct         l.this,         l.that,         l.other         [db_name].[schema_name].[view_name] l         1=1';     declare @params nvarchar(max);     set @params = n'         @foo nvarchar(max) = null     ,@bar nvarchar(max) = null     ,@spam nvarchar(max) = null     ,@eggs nvarchar(max) = null     ';     set @foo = '%' + @foo + '%';     set @sqlcmd = @sqlcmd + case when          @foo          not null         n' , l.foo @foo'         else '' end;     set @bar = '%' + @bar + '%';     set @sqlcmd = @sqlcmd + case when          @bar          not null         n' , l.bar @bar'         else '' end;     set @spam = '%' + @spam + '%';     set @sqlcmd = @sqlcmd + case when          @spam         not null         n' , l.spam @spam'         else '' end;     set @eggs = '%' + @eggs + '%';     set @sqlcmd = @sqlcmd + case when          @eggs         not null         n' , l.eggs @eggs'         else '' end;     execute sp_executesql @sqlcmd, @params, @foo, @bar, @spam, @eggs; end 

guts of function:

use [db_name] go set ansi_nulls on go set quoted_identifier on go alter function [schema_name].[function_name] (     @foo nvarchar(max) = null     ,@bar nvarchar(max) = null     ,@spam nvarchar(max) = null     ,@eggs nvarchar(max) = null ) returns table return(     select distinct         l.this,         l.that,         l.other     [db_name].[schema_name].[view_name] l              l.foo isnull('%'+@foo+'%', '')         , l.bar isnull('%'+@bar+'%', '')         , l.spam isnull('%'+@spam+'%', '')         , l.eggs isnull('%'+@eggs+'%', '') ); 

try in function:

where   1 = case     when @foo null 1     when l.foo isnull('%'+@foo+'%') 1   end   , 1 = case     when @bar null 1     when l.bar isnull('%'+@bar+'%') 1   end   , 1 = case     when @spam null 1     when l.foo isnull('%'+@spam+'%') 1   end   , 1 = case     when @eggs null 1     when l.eggs isnull('%'+eggs+'%') 1   end