CODEWITHSUNDEEP
sql-server
NGoyal
NGoyal

Reputation: 190

COALESCE to get NULL as well as NOT NULL values in SQL Server 2008

I have a query written in MySQL as :

select RecordID, AppInstanceID, ID
 where RecordID like ? 
 and((isNull(AppInstanceID) OR AppInstanceID=  CASE  WHEN COALESCE(?,'') = ''  THEN AppInstanceID  ELSE ?  END)
 and(isNull(ID) OR ID=  CASE  WHEN COALESCE(?,'') = ''  THEN    ID  ELSE ?  END)

This gives me Null as well as Not Null Values.

Now, I m shifting to SQL Server 2008.

How can I achieve this coalesce functionality in SQL Server 2008.

User can pass % in recordId field and he should get all data, either null as well as not null.

Upvotes: 1

Views: 204

Answers (3)

OGHaza
OGHaza

Reputation: 4795

I'm not entirely sure what your question is, so this may be an answer, but it is certainly advice. You can rewrite your where clause as follows:

SELECT RecordID, AppInstanceID, ID
WHERE RecordID LIKE ? 
AND (AppInstanceID IS NULL OR AppInstanceID = ISNULL(?, AppInstanceID))
AND (ID IS NULL OR ID = ISNULL(?, ID)

CASE WHEN COALESCE(?,'') = '' THEN ID ELSE ? END

Is exactly the same as writing

COALESCE(?, ID)

The CASE statement is completely unnecessary. (And COALESCE can be swapped for ISNULL as in my code above)

Upvotes: 2

NGoyal
NGoyal

Reputation: 190

I have found the solution. Here's how it works.

select RecordID, AppInstanceID, ID
 where RecordID like ? 
 and(AppInstanceID=  CASE  WHEN COALESCE(?,'') = ''  THEN AppInstanceID  ELSE ?  END)
 and(ID=  CASE  WHEN COALESCE(?,'') = ''  THEN    ID  ELSE ?  END)

Thanks

Upvotes: 0

M.Ali
M.Ali

Reputation: 69504

If you want all the NULL and NOT NULL values you dont have to put anything in your where Clause, just SELECT the columns you want and it will return all the values regardless of if thery are NULL or NOT NULL.
But if your using Like operator to filter out some results and then you also want all the NULL values you could do something like this. 

select RecordID, AppInstanceID, ID
WHERE RecordID like '%'
OR RecordID IS NULL

It will return all the rows where RecordID has some value Like defined by you or NULL values.

Upvotes: 0

Related Questions