MAX dont know what type is returning

Hi guys,

I'm doing some function in the SQLExpress using the max func and I cant figure out if its returning null or empty string. It always return null even it's returning index.

Please help.

Here's is the sample data of the table that I'm manipuilating

code name

code name

code_1 name_1

code_2 name_2

tues_2 name_4

code_4 name_3

>>> What am trying to get is the maximum index of the code

Below is my function

declare @.codeIndex varchar(20)

select @.codeIndex = isnull(max(isnull(substring([code],len('code')+2), len'code')) ,0)),0) from table where [code] like 'code%'

print @.codeIndex

if @.codeIndex is null

begin

print 'Code index is null

end

else

begin

print 'we got an index!'

end

>>> the output of this is always null even it's returning an index

Can you post a full repro? You code didn't compile (not correct number of parameters etc). Either against the pubs database or also post CREATE TABLE and some INSERT statements. I changed the code so it at least compiled (but I doubt it has correct logic), and there doesn't seem to anything wrong with the MAX function per se.: declare @.codeIndex varchar(20) select @.codeIndex = isnull(max(isnull(substring([au_lname],len('code')+2, len('code')) ,0)),0) from authors print @.codeIndex if @.codeIndex is null begin print 'Code index is null' end else begin print 'we got an index!' end -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:b4844e9a-27a2-49d0-8a11-482daa3423db@.discussions.microsoft.com...
> Hi guys, >
> I'm doing some function in the SQLExpress using the max func and I
> cant figure out if its returning null or empty string. It always return
> null even it's returning index. >
> Please help. >
> Here's is the sample data of the table that I'm manipuilating >
> code name >
> code name >
> code_1 name_1 >
> code_2 name_2 >
> tues_2 name_4 >
> code_4 name_3 > > > >>>> What am trying to get is the maximum index of the code >
> Below is my function >
> declare @.codeIndex varchar(20) >
> select @.codeIndex = isnull(max(isnull(substring([code],len('code')+2),
> len'code')) ,0)),0) from table where [code] like 'code%' > > >
> print @.codeIndex >
> if @.codeIndex is null >
> begin >
> print 'Code index is null >
> end >
> else >
> begin >
> print 'we got an index!' >
> end > >>>> the output of this is always null even it's returning an index >
>|||

Hi NNTP,

Thanks! I already figured it out; maybe, I'm just exhausted last friday.