asp.net - Stored procedure takes too much time in if statement when i pass @ip='' and takes 0 sec with some value to @IP -

i have below sp , taking time , stopped

exec memberlisting 1,20,'','','regdate','desc',0 

while works if pass value @ip parameter. don't know why.

create procedure memberlisting     @pagenum int,     @perpageresult int,     @username nvarchar(50),     @ip varchar(50),     @sortcolumn varchar(50),     @sortorder varchar(4),     @totalcount int output begin        declare @temp table(rownum int, id bigint, username nvarchar(50), email nvarchar(50), regdate datetime, country varchar(25),                             lastlogin datetime, isgoldmember varchar(1))     declare @sort varchar(50)     if @ip = ''         begin             print 'if'             set fmtonly off;             insert @temp             select row_number() on (order               case when @sortorder <> 'asc' 0 when @sortcolumn = 'id' id end asc         ,   case when @sortorder <> 'asc' 0 when @sortcolumn = 'username' username end asc         ,   case when @sortorder <> 'asc' 0 when @sortcolumn = 'email' email end asc         ,   case when @sortorder <> 'asc' cast(null datetime) when @sortcolumn = 'regdate' regdate end asc         ,   case when @sortorder <> 'asc' 0 when @sortcolumn = 'country' country end asc         ,   case when @sortorder <> 'asc' cast(null datetime) when @sortcolumn = 'lastlogin' lastlogin end asc         ,   case when @sortorder <> 'asc' 0 when @sortcolumn = 'isgoldmember' isgoldmember end asc          ,   case when @sortorder <> 'desc' 0 when @sortcolumn = 'id' id end desc         ,   case when @sortorder <> 'desc' 0 when @sortcolumn = 'username' username end desc         ,   case when @sortorder <> 'desc' 0 when @sortcolumn = 'email' email end desc         ,   case when @sortorder <> 'desc' cast(null datetime) when @sortcolumn = 'regdate' regdate end desc         ,   case when @sortorder <> 'desc' 0 when @sortcolumn = 'country' country end desc         ,   case when @sortorder <> 'desc' cast(null datetime) when @sortcolumn = 'lastlogin' lastlogin end desc         ,   case when @sortorder <> 'desc' 0 when @sortcolumn = 'isgoldmember' isgoldmember end desc             ) rownum, * (                select                  m.id,m.login username,m.email email,m.registrationdate regdate,c.name country,m.lastlogindate lastlogin,                 case when (r.description='goldmember' or r.description='goldmember_forever') 1 end isgoldmember              member m              join country c on m.country_id = c.id             join user_role ur on m.id=ur.member_id             join role r on r.id=ur.role_id             left join lastip l on m.id=l.user_id             r.description <> 'administrator' , m.login @username+'%'             ) aab             select * @temp rownum> @pagenum-1 , rownum<@perpageresult+@pagenum             order              case when @sortorder <> 'asc' 0 when @sortcolumn = 'id' id end asc         ,   case when @sortorder <> 'asc' 0 when @sortcolumn = 'username' username end asc         ,   case when @sortorder <> 'asc' 0 when @sortcolumn = 'email' email end asc         ,   case when @sortorder <> 'asc' cast(null datetime) when @sortcolumn = 'regdate' regdate end asc         ,   case when @sortorder <> 'asc' 0 when @sortcolumn = 'country' country end asc         ,   case when @sortorder <> 'asc' cast(null datetime) when @sortcolumn = 'lastlogin' lastlogin end asc         ,   case when @sortorder <> 'asc' 0 when @sortcolumn = 'isgoldmember' isgoldmember end asc          ,   case when @sortorder <> 'desc' 0 when @sortcolumn = 'id' id end desc         ,   case when @sortorder <> 'desc' 0 when @sortcolumn = 'username' username end desc         ,   case when @sortorder <> 'desc' 0 when @sortcolumn = 'email' email end desc         ,   case when @sortorder <> 'desc' cast(null datetime) when @sortcolumn = 'regdate' regdate end desc         ,   case when @sortorder <> 'desc' 0 when @sortcolumn = 'country' country end desc         ,   case when @sortorder <> 'desc' cast(null datetime) when @sortcolumn = 'lastlogin' lastlogin end desc         ,   case when @sortorder <> 'desc' 0 when @sortcolumn = 'isgoldmember' isgoldmember end desc         end     else         begin             print 'else'             set fmtonly off;             insert @temp             select row_number() on (order               case when @sortorder <> 'asc' 0 when @sortcolumn = 'id' id end asc         ,   case when @sortorder <> 'asc' 0 when @sortcolumn = 'username' username end asc         ,   case when @sortorder <> 'asc' 0 when @sortcolumn = 'email' email end asc         ,   case when @sortorder <> 'asc' cast(null datetime) when @sortcolumn = 'regdate' regdate end asc         ,   case when @sortorder <> 'asc' 0 when @sortcolumn = 'country' country end asc         ,   case when @sortorder <> 'asc' cast(null datetime) when @sortcolumn = 'lastlogin' lastlogin end asc         ,   case when @sortorder <> 'asc' 0 when @sortcolumn = 'isgoldmember' isgoldmember end asc          ,   case when @sortorder <> 'desc' 0 when @sortcolumn = 'id' id end desc         ,   case when @sortorder <> 'desc' 0 when @sortcolumn = 'username' username end desc         ,   case when @sortorder <> 'desc' 0 when @sortcolumn = 'email' email end desc         ,   case when @sortorder <> 'desc' cast(null datetime) when @sortcolumn = 'regdate' regdate end desc         ,   case when @sortorder <> 'desc' 0 when @sortcolumn = 'country' country end desc         ,   case when @sortorder <> 'desc' cast(null datetime) when @sortcolumn = 'lastlogin' lastlogin end desc         ,   case when @sortorder <> 'desc' 0 when @sortcolumn = 'isgoldmember' isgoldmember end desc             ) rownum, * (                select                  m.id,m.login username,m.email email,m.registrationdate regdate,c.name country,m.lastlogindate lastlogin,                 case when (r.description='goldmember' or r.description='goldmember_forever') 1 end isgoldmember              member m              join country c on m.country_id = c.id             join user_role ur on m.id=ur.member_id             join role r on r.id=ur.role_id             left join lastip l on m.id=l.user_id             r.description <> 'administrator' , m.login @username+'%' , l.address = @ip             ) aa             select * @temp rownum> @pagenum-1 , rownum<@perpageresult+@pagenum             order              case when @sortorder <> 'asc' 0 when @sortcolumn = 'id' id end asc         ,   case when @sortorder <> 'asc' 0 when @sortcolumn = 'username' username end asc         ,   case when @sortorder <> 'asc' 0 when @sortcolumn = 'email' email end asc         ,   case when @sortorder <> 'asc' cast(null datetime) when @sortcolumn = 'regdate' regdate end asc         ,   case when @sortorder <> 'asc' 0 when @sortcolumn = 'country' country end asc         ,   case when @sortorder <> 'asc' cast(null datetime) when @sortcolumn = 'lastlogin' lastlogin end asc         ,   case when @sortorder <> 'asc' 0 when @sortcolumn = 'isgoldmember' isgoldmember end asc          ,   case when @sortorder <> 'desc' 0 when @sortcolumn = 'id' id end desc         ,   case when @sortorder <> 'desc' 0 when @sortcolumn = 'username' username end desc         ,   case when @sortorder <> 'desc' 0 when @sortcolumn = 'email' email end desc         ,   case when @sortorder <> 'desc' cast(null datetime) when @sortcolumn = 'regdate' regdate end desc         ,   case when @sortorder <> 'desc' 0 when @sortcolumn = 'country' country end desc         ,   case when @sortorder <> 'desc' cast(null datetime) when @sortcolumn = 'lastlogin' lastlogin end desc         ,   case when @sortorder <> 'desc' 0 when @sortcolumn = 'isgoldmember' isgoldmember end desc         end  end 

i below error in case pass '' @ip parameter.

if msg 8152, level 16, state 2, procedure memberlisting, line 24 string or binary data truncated. statement has been terminated.  (0 row(s) affected) 

as wrote in comment answer of first question is:

if understand prc well, in case of @ip = '' should returns wieder set of members. think possible username, email or country longer in member table @temp table's definition. match column lenghts of @temp wit table member.

second answer question:

in case @ip='some value', takes 0 secs , in case @ip='', takes 1:50 min. what's wrong here?

i think should cut prc 2 part. run both query (with @ip = '' , = 'somevalue') , check execution plans.

if see, working 'somevalue' , there no need of index, think query victim of parameter sniffing, means execution plan optimized retrieve possibilites , not optimal make more selective search.

probably optimized full table scan described in article linked.