Click here to Skip to main content
12,764,666 members (38,838 online)
Rate this:
 
Please Sign up or sign in to vote.
In my table has integer column for id

I am using Isnull() function for select maximum id like
select max(isnull(id,0))+1 from table

But it is not working. When the table is null it displays null null value.
I didn't know Why?

Please help me.
Posted 15-Aug-12 21:26pm
devausha1.5K
Updated 15-Aug-12 21:35pm
Mehdi Gholam332.9K
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

select isnull(max(id),0)+1 from table
  Permalink  
Comments
Mehdi Gholam 16-Aug-12 2:35am
   
5'ed
AlluvialDeposit 16-Aug-12 2:39am
   
perfect :) 5'ed
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

In your code, you select max(no records), which is (correctly) null.

Use isnull(max(id),0)+1

If the table is empty, max(id) will return null, and isnull will replace it by zero.

Hope this helps,

Pablo.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

If the table contans no records, your query will not return any result rows hence you're not getting any macimum value.

One way to do this is to use a scalar. So for example something like this:
select coalesce( (select max(id) from YourTable), 0)
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.170217.1 | Last Updated 16 Aug 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100