Sql server query return “no data” in a row if no data found

Question

1.00/5 (1 vote)

See more:

My code able to retrieve book code data that match with the barcode but not when there is no data match. I want it to print "Bookcode not found" in the cell also if there's no data found.

What I have tried:

I tried with one-by-one barcode in sql server. If I put only one barcode example '123' that do not have data then it managed to display 'Bookcode not found' but if I put 3 other barcodes that have data then the 'Bookcode not found' will not be included. I really appreciate your help.

WITH cte AS
(SELECT *,ROW_NUMBER() OVER(PARTITION BY Barcode ORDER BY InvtID Asc) rid 
FROM [Danny].[dbo].[InventoryCustomer] WHERE Barcode In ('ean','9789830093819'))
SELECT InvtID, BOOKCODE = coalesce(InvtID, 'Bookcode not found') 
FROM cte WHERE rid=1 UNION SELECT InvtID = '', BOOKCODE = 'Bookcode not found' 
WHERE NOT EXISTS(SELECT 1 FROM CTE)

Posted 7-Feb-19 20:34pm

Member 14127871

Updated 7-Feb-19 22:52pm

Add a Solution

Comments

Santosh kumar Pithani 8-Feb-19 3:39am

Any sample data and expected output?

Member 14127871 8-Feb-19 3:50am

Santosh kumar Pithani 8-Feb-19 4:05am

Are you using SQL Server 2016?

Member 14127871 8-Feb-19 4:31am

Sql server 2008

Member 14127871 10-Feb-19 22:55pm

This are all the query I have tried but none of it will display 'no data' if no rows are found in sql. It basically just display existed data only

1. 'SELECT isnull((SELECT InvtID
FROM InventoryCustomer WHERE Barcode IN('123','9789830093819')),'No bookcode found')
AS InvtID '

2. 'Select case when s.InvtID IS NOT NULL Then s.InvtID else 'no data' end as Bookcode
from (Select InvtID as InvtID FROM InventoryCustomer WHERE Barcode IN('123','9789830093819')) R
Left Join InventoryCustomer s ON s.InvtID = R.InvtID'

3. 'IF NOT EXISTS (Select InvtID From InventoryCustomer WHERE Barcode in ('123','9789830093819'))
Begin SELECT 'Bookcode not found' as Bookcode end
ELSE
SELECT InvtID From InventoryCustomer WHERE Barcode in ('123','9789830093819')'

4. 'SELECT InvtID, CASE WHEN InvtID is null OR InvtID='' THEN 'no data'
ELSE InvtID
END AS Bookcode
FROM InventoryCustomer WHERE Barcode in ('123','9789830093819')'

5. 'WITH cte AS(SELECT case WHEN InvtID IS NULL OR InvtID='' THEN 'No Bookcode Found'
ELSE InvtID END AS InvtID,Barcode,ROW_NUMBER() OVER(PARTITION BY Barcode ORDER BY InvtID Asc)
rid FROM InventoryCustomer)
SELECT InvtID AS BOOKCODE FROM cte WHERE rid=1 and Barcode In ('123','9789830093819')'

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2019-02-07T22:10:00

Solution 2

Maciej solution will work, but ... this isn't really a data retrieval function, it's a presentation function and it would be both much better and more efficient to do this in your presentation language.

It's simple there: retrieve the rows, count them, and take the appropriate action. Doing it in SQL returns a variable number of columns, which is messy for presentation.

Maciej's solution will work, but it means effectively running the query twice, which is both inefficient and risks problems if the query needs to be modified.

Posted 7-Feb-19 22:10pm

OriginalGriff

Comments

Maciej Los 8-Feb-19 5:23am

I was waiting for that... :) Thanks. It's most valuable comment posted in a solution.

What you think about below form?

 SELECT ISNULL(BookCode, 'Not found'), Field2, Field3, ...
FROM TableName
WHERE BookCode = '12345'

OriginalGriff 8-Feb-19 5:36am

Returns no rows because the WHERE returns no rows, rather than a row of null values. You could do it with a JOIN, I think - but it's still a messy solution to a presentation problem.

MadMyche 8-Feb-19 7:31am

The KEY term in this answer is it's a presentation function. The OP should run the query and if no rows are returned then you display the Not Found message.

Maciej Los 8-Feb-19 7:44am

Totally agree.

Member 14127871 11-Feb-19 1:45am

Can you explain more about presentation function?

OriginalGriff 11-Feb-19 3:28am

Have a look here:
https://en.wikipedia.org/wiki/Multitier_architecture
SQL is part of the Data layer, and performs Data functions such as fetch and store.
What you are trying to do is concerned with how that data is shown to the user - it's part of the presentation layer.

It may sound arbitrary and unnecessary, but it makes your code simpler, easier to understand, and more reliable. It also means that when you need to change devices or databases, it's a whole load easier!

Maciej Los · Answer 2 · 2019-02-07T21:27:00

Solution 1

In a short: you can use IF[^] + EXISTS[^]:

SQL

IF NOT EXISTS
(
    SELECT TOP 1 Barcode FROM [Danny].[dbo].[InventoryCustomer] WHERE Barcode In ('ean','9789830093819'))
) 
   SELECT 'Bookcode not found'
ELSE
    SELECT Your_Complex_Query_Here
END

Posted 7-Feb-19 21:27pm

Maciej Los

Updated 10-Feb-19 20:55pm

v2

Comments

Member 14127871 10-Feb-19 23:07pm

I tried this but still unable to display 'no data' if no rows are found in sql. I list all the queries I tried at the comment above

Maciej Los 11-Feb-19 2:55am

Check updated answer.

Member 14127871 11-Feb-19 4:12am

Doesnt works also. I already tried coalesce, isnull, if exist, case when, union, left join same results

Maciej Los 11-Feb-19 4:26am

So, there's someting wrong with initial query...
Note that i have no access to your data and don't see your screen, so i can't help you without knowing very specific information.

Santosh kumar Pithani · Answer 3 · 2019-02-07T22:52:00

Solution 4

SQL

--Actually Table records is filtered by values but you including filtered values as records.This solution is given based on output,i hope this helpful 

CREATE TABLE #InventoryCustomer(InvtID Varchar(20),Bookcode Varchar(20),BarCode Varchar(50)) 

INSERT INTO #InventoryCustomer
Values
      ('CB0212','CB0212','ean'),
      ('DD2921','DD2921','9789830093819');

--In 2008 server need to create split_function for splitting  values.

select 
  ISNULL(InvtID,'') AS InvtID
 ,ISNULL(Bookcode,'Bookcode not found') AS Bookcode 

   FROM split_fun('1,ean,9789830093819',',') AS Stv LEFT JOIN #InventoryCustomer cte   
       ON( Stv.value=Cte.Barcode)

OUTPUT:
-------------------
InvtID | Bookcode
--------------------
	Bookcode not found
CB0212	CB0212
DD2921	DD2921

Posted 7-Feb-19 22:52pm

Santosh kumar Pithani

Comments

CHill60 8-Feb-19 8:12am

Better would be

select 
 ISNULL(InvtID,Stv.[value]) AS InvtID,
 ISNULL(Bookcode,'Bookcode not found') AS Bookcode 
FROM string_split('1,ean,9789830093819',',') AS Stv 
LEFT JOIN #InventoryCustomer cte   ON Stv.value=Cte.Barcode

which would give the results

InvtID  Bookcode
-----------------
1	Bookcode not found
CB0212	CB0212
DD2921	DD2921

Santosh kumar Pithani 8-Feb-19 8:57am

What is the use to post same solution in comment?

CHill60 8-Feb-19 9:14am

It is not the same solution. I changed your

ISNULL(InvtID,0)

to

ISNULL(InvtID,Stv.[value])

as evidenced by the change in the output.
Otherwise you can't easily determine which bookcodes were not found

Member 14127871 10-Feb-19 21:52pm

Is there other alternative other than use string_split as I afraid other database would be affected if I change the compatibility of my sql server to other level to use that function. It is fine actually to get the Bookcode column only without the InvtID column

Santosh kumar Pithani 10-Feb-19 23:31pm

If you afraid to use string split then take table type parameter for that its a perfect solution.

Create Type Temp as Table(Barcode Varchar(100));
Insert parameters(1,ean,9789830093819) into Table Type from application side .
Pass this Table type as Stored Procedure parameter.

Declare @Temp Temp ;

select
ISNULL(InvtID,'') AS InvtID
,ISNULL(Bookcode,'Bookcode not found') AS Bookcode

FROM @Temp AS Stv LEFT JOIN #InventoryCustomer cte
ON( Stv.value=Cte.Barcode)

CHill60 11-Feb-19 3:55am

Instead of using the built-in function in later versions of SQL most of us have a User-Defined Function that does the same thing. I "borrowed" the one I use from SQLServerCentral.com but there are others listed here … Split strings the right way - or the next best way[^]