Difference between COALESCE and ISNULL function in SQL Server

ISNULL()

Syntax: ISNULL(expression, Replace_value )

--It takes two parameter

--SQL Server Specific

--Return 1^st parameter if it is not null.

Example:

SELECT ISNULL(NULL, 'SQLServer');

Result: SQLServer

SELECT ISNULL('Inside', 'Forum');

Result: Inside

SELECT ISNULL(NULL, 10);

Result: 10

SELECT ISNULL(18, 88);

Result: 18

SELECT ISNULL(NULL, '2015-10-20');

Result: '2015-10-20'

COALESCE()

Syntax: COALESCE( expression1, expression2, ... expression_n )

-It takes multiple parameters

-ANSI Standard

-Return 1st parameter if it is Not NULL.

Example:

SELECT COALESCE(NULL, 'SQL Server', 'Inside');

Result: SQL Server

SELECT COALESCE(NULL, NULL, 1, 2, 3, NULL, 4);

Result: 1

Difference:

COALESCE and ISNULL function is used to return the first non-null expression.

Here is the few differences:

1. COALESCE is a ANSI standard where ISNULL is SQL Server Specific

2. COALESCE can accept multiple parameters whereas, ISNULL can accept only two parameters

declare @first as int=NULL

declare @sec as int=1234

declare @third as int=12

select COALESCE(@first,@sec,@third) as ColParam, ISNULL(@first,@sec) as IsNUllParam

3. COALESCE will not support two NULL Parameters while ISNULL will support

select COALESCE(NULL,NULL) as ColParam, ISNULL(NULL,NULL) as IsNUllParam

4. COALESCE will not depend on the length of the datatype, it will return the whole string

ISNULL depends on the length of the first datatype.

declare @first as varchar(3)=NULL

declare @sec as varchar(10)='123456'

declare @third as varchar(4)='5689'

select COALESCE(@first,@sec,@third) as ColParam, ISNULL(@first,@sec) as IsNUllParam, ISNULL(@first,@third) as IsNUllParam1

Unable to open Step output file. The step failed

When you try to execute the job, The job will fail with error "Unable to open Step output file. The step failed"

In this case there is no error with the package. So we have to change the path in jobs under sql agent job.

For example if the job name is: Batch_price.

Here is the steps:

1. Double click on Batch_price in sql agent job. [i.e. job properties]

2. Go to steps.

3. Edit steps.

4.Click advanced option.

5.Change the path under output file as in image below or delete the path.

6. ok and run the job again.