Increment int by 1 in sql server?

Question

How to return 1

if SQL below returns NULL

?

Something like (pseudocode):

if sql return NULL value 
then set value to one 
otherwise returning sql result value.

Is there a SQL to define a default value of 1 if the SQL result is NULL?

SELECT  Max(iCategoryOrder)+1 
FROM    [IVRFlowManager].[dbo].[tblCategory] 
WHERE   iCategoryLevel = 1

+3

Shrestha sunil Apr 10 12 at 11:38

2 answers

Option 1

Use ISNULL()

Description

Replaces a null value with the specified replacement value.

SELECT    MAX(ISNULL(iCategoryOrder, 0))+1 
FROM      [IVRFlowManager].[dbo].[tblCategory] 
WHERE     iCategoryLevel = 1

Option 2

Use COALESCE()

SELECT    MAX(COALESCE(iCategoryOrder, 0))+1 
FROM      [IVRFlowManager].[dbo].[tblCategory] 
WHERE     iCategoryLevel = 1

Description

Returns the first non-null expression among its arguments.

+5

James hill Apr 10 12 at 11:40

Andrey Gurinov · Accepted Answer · 2012-04-10T11:39:52+0000

Use the ISNULL operator, for example:

ISNULL(your_field, 1)

Try the following:

Select ISNULL(Max(iCategoryOrder), 0) + 1  
from [IVRFlowManager].[dbo].[tblCategory]  
where iCategoryLevel = 1